Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Mar 17, 2022

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...
  • George_Hepworth's avatar
    Mar 18, 2022

    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.

     

Resources