Forum Discussion
Tony2021
Mar 17, 2022Steel Contributor
If Date is Null then use other field
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 tri...
- Mar 18, 2022
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.
George_Hepworth
Mar 18, 2022Silver Contributor
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.
- Tony2021Mar 18, 2022Steel Contributor
Hi George, perfect. that worked as intended and I tested it thoroughly. Noted on the time. It seems to work without issues.