If Date is Null then use other field

[Tony2021]

Hello Experts, 

How can I modify the BETWEEN in the pic below to calculate to use

If DateOfIssue is Null then use DateOfExpectedIssue

if DateOfIssue is Not Null then use DateOfIssue

I have tried to modify but not sure if the following logic is correct: Nz([DateOfIssue],0) since not sure if a date can have a 0 value.  

the dte part below is only there for a bigger picture and dont think its necessary to know since only need the between to be modified. 

thank you

[#2]

your expression is valid and access will accept it.
Date datatype is really saved internally as Double.
the Integer part is the Date and the fractional part is the Time.

[#3 · Accepted solution]

@Tony2021 

So you have two fields in the table, "DateOfIssue" and "DateOfExpectedIssue"

I assume that you enter the latter value as a guess about the issue date when a record is created, and fill in the actual value when the item is actually issued.

So, you want to select DateOfIssue if that has actually occurred, and the guessed DateOfExpectedIssue if it has not yet occurred.

You can use the Immediate If expression.

Iif([DateOfIssue] Is Null, [DateOfExpectedIssue], [DateOfIssue])

So, the full criteria would be:

Between Iif([DateOfIssue] Is Null, [DateOfExpectedIssue], [DateOfIssue]) AND [UltimateExpirey]

Beware, though, as Arnel pointed out, that dates ALWAYS include a time component, whether it is formatted to display or not. Depending on how your various dates were entered -- using Date() or using Now(), for example -- your criteria might not work 100% correctly due to the presence of a time that you don't see in the formatted value. 

Check that out carefully and post back for more details if it is an issue here.

[#4]

Hi George, perfect. that worked as intended and I tested it thoroughly.  Noted on the time. It seems to work without issues.