Between Dates with Null values

[Salty Mariner]

I have an application that needs to determine crew members who were on between certain dates. I have two data fields named [SignOn] and [SignOff]. I have a criteria field in my query that gets its date from a textbox on a form txtReportDate. My WHERE Statement is as follows;

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

This works great EXCEPT when I have to deal with a Null Sign Off value. I have tried Using OR Is Null with [SignOff] But then this disregards my criteria for SignOn and just returns all records.

What am I missing here? I have racked my brains out trying to make this work. I simply want to return all records BETWEEN [SignOn] and [SignOff] and to also include those with Null values for [SignOff] if they exist. Nothing I have tried has returned the desired results.

[CJ_London]

Try

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

[Salty Mariner]

Thank you for the response but the query still returns all of those with a null [SignOff] date even if I go back a few months with my criteria a few months before they had an active [SignOn] date.

Iam wondering if I might have to do something weird like create two separate queries, one for each condition and then conditionally exclude the unwanted values. I've never done anything like this before but it seems like the next logical step if this doesn't work would be to break this into smaller pieces and then try to combine them. I've even tried things suggested by Microsoft Co-Pilot AI and none have panned out so far. I shouldn't be this hard.

[pbaldy]

I was thinking more like:

Between [SignOn] And Nz([SignOff], #12/31/2029#)

[CJ_London]

but the query still returns all of those with a null [SignOff] date

you said

I simply want to return all records BETWEEN [SignOn] and [SignOff] and to also include those with Null values for [SignOff] if they exist.

So what are you expecting?

[Salty Mariner]

pbaldy,

Thank you! THAT was the ticket.

I put far too many hours into frying my brain cells trying to figure out what turned out to be such a simple and elegant solution.

[Salty Mariner]

CJ_London,

pbaldy refined this to where it works now. Thank you for your time and response.

[pbaldy]

pbaldy,

Thank you! THAT was the ticket.

I put far too many hours into frying my brain cells trying to figure out what turned out to be such a simple and elegant solution.

Happy to help, and welcome to the site by the way!

[Welshgasman]

TBH I cannot see why CJ_London's would not work?

All it is doing is replacing a null date with the criteria date. Pbaldy's solution does exactly the same thing, just with a hardcoded date?

If you are still using this program in 2030, the latter will fail?

However the logic is the same?

[CJ_London]

on reflection, both methods are not right as missing the date delimiters - and depending on where the OP is, reformatting the date to the SQL or US standard

WHERE "#" & [Forms]![frmVesselReport]![txtReportDate] & "#" Between [SignOn] And nz([SignOff],"#" & [Forms]![frmVesselReport]![txtReportDate] & "#")

or if date requires reformatting

WHERE "#" & format([Forms]![frmVesselReport]![txtReportDate],"yyyy-mm-dd") & "#" Between [SignOn] And nz([SignOff],"#" & format([Forms]![frmVesselReport]![txtReportDate],"yyyy-mm-dd") & "#")

[Welshgasman]

on reflection, both methods are not right as missing the date delimiters - and depending on where the OP is, reformatting the date to the SQL or US standard

WHERE "#" & [Forms]![frmVesselReport]![txtReportDate] & "#" Between [SignOn] And nz([SignOff],"#" & [Forms]![frmVesselReport]![txtReportDate] & "#")

or if date requires reformatting

WHERE "#" & format([Forms]![frmVesselReport]![txtReportDate],"yyyy-mm-dd") & "#" Between [SignOn] And nz([SignOff],"#" & format([Forms]![frmVesselReport]![txtReportDate],"yyyy-mm-dd") & "#")

I thought that was only for Dates as strings?

SELECT tblDaily.*, tblDaily.DailyDate
FROM tblDaily
WHERE (((tblDaily.DailyDate) Between [Forms]![frmDailyEntry]![txtDate] And Date()));

works fine?

[CJ_London]

I thought that was only for Dates as strings?

which is what a textbox control datatype is.

But if it works, it works

[Salty Mariner]

TBH I cannot see why CJ_London's would not work?

All it is doing is replacing a null date with the criteria date. Pbaldy's solution does exactly the same thing, just with a hardcoded date?

If you are still using this program in 2030, the latter will fail?

However the logic is the same?

I agree the Logic seems to be the same, and I don't understand why either. But with some of this stuff logic doesn't always seem to apply. Many of the suggested examples I found through searches Seemed like the logic was sound but somehow they also failed. I'm guessing this where some of you guys who have been at this longer than others have an edge. At some point you had to struggle and learn things the hard way too which in turn influenced how you choose to approach things. I'm guessing that the hard coded end date can pushed further into the future to stave off obsolescence. In any event I appreciate your concern and assistance. I usually try to figure things out myself but sometimes I just get stuck.

Thank you again.

[moke123]

I'm guessing that the hard coded end date can pushed further into the future to stave off obsolescence.

You could also use something like

Between [SignOn] And Nz([SignOff], DateAdd("y",50,date))

which will never expire.

[pbaldy]

All it is doing is replacing a null date with the criteria date. Pbaldy's solution does exactly the same thing, just with a hardcoded date?

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.