Between Dates with Null values

[Welshgasman]

No, I replace the null date with a date in the future. Comparing the criteria date to itself will result in all records being returned.

Moke's suggestion is a good one.

Yes I realise that, but that date should still be effective?

[pbaldy]

Yes I realise that, but that date should still be effective?

The quirky part here is that the criteria entered by the user is where there would typically be a field name:

WHERE ((([Forms]![frmVesselReport]![txtReportDate]) Between [SignOn] And [SignOff]))

Thus if you use the criteria date you get (for example):

WHERE 12/22/23 Between [SignOn] And 12/22/23

In a sense comparing the criteria to itself. Every record meets the criteria. I used a future date:

WHERE 12/22/23 Between [SignOn] And 12/31/29

so only records with a SignOn date before the criteria qualify.

But then again my brain is fried.

[CJ_London]

Thus if you use the criteria date you get (for example):

WHERE 12/22/23 Between [SignOn] And 12/22/23

but that only happens if signoff is null. If the signoff date is populated, then that value is being used.

OP wanted all records where the specified date is between signon and signoff or is >=signon and signoff is null

OP mentioned tried using OR - that would be the other way to go

WHERE signOn<=[specified date] AND (signoff>=[specified date] OR [specified date] is null)

[Salty Mariner]

You could also use something likeCode:
Between [SignOn] And Nz([SignOff], DateAdd("y",50,date))
which will never expire.

Moke,

I like it. You guys are great!

Thanks.

[Salty Mariner]

but that only happens if signoff is null. If the signoff date is populated, then that value is being used.

OP wanted all records where the specified date is between signon and signoff or is >=signon and signoff is null

OP mentioned tried using OR - that would be the other way to go

WHERE signOn<=[specified date] AND (signoff>=[specified date] OR [specified date] is null)

This was my initial inclination (using OR) However, I was unable to get the desired results, and it wasn't for lack of trying. Although it may have been my lack of experience as I am not a full-time developer... more of a "hobbyist" who who likes to create solutions for my work to help make peoples life easier.