Search code examples
sqlms-accessms-access-2016iif

Custom Field in Query for MS Access Issue

I am trying to put a custom field in a query. My other 2 have come off without a hitch, but I cannot for the life of me figure out what I am missing to get this one to work. I've tried dozens of different permutations of code. I am trying to take either DateDiff("d",Date(),[REV_PRM_DT]) if there is anything in that column, or DateDiff("d",Date(),[X65-SCHATS]) if there is not. With the caveats that if there is anything in [Green_Date] it should return "" and if the value in [REV_PRM_DT] is 01/01/2055 it should return "" (we use this as a filler to get it to not show up other places).

I'm hoping I'm pulling a stupid and missing something obvious.Showing incorrect behavior.

Some examples of things I have tried are:

Days to ATS:iif(([REV_PRM_DT]<>"01/01/2055") and ([Green_Date] is null),iif(([REV_PRM_DT] is not null),DateDiff("d",Date(),[REV_PRM_DT]),DateDiff("d",Date(),[X65-SCHATS])),"")

Days_to_ATS:iif(([REV_PRM_DT]<>"01/01/2055") and ([Green_Date] is null),iif(([REV_PRM_DT] is not null),DateDiff("d",Date(),[REV_PRM_DT]),DateDiff("d",Date(),[X65-SCHATS])),"")

Days_to_ATS:iif([Green_Date] is null,iif([REV_PRM_DT]="01/01/2055","",iif([REV_PRM_DT]="",DateDiff("d",Date(),[X65-SCHATS]),DateDiff("d",Date(),[REV_PRM_DT]))),"")

Solution

  • I presume these fields are date/time data type. Since expression should return a number value, don't return "", return Null or 0. Use # instead of quote for date delimiter. More parens than needed.

    IIf([REV_PRM_DT] <> #01/01/2055# AND [Green_Date] Is Null, 
   IIf(Not [REV_PRM_DT] Is Null, DateDiff("d", Date(), [REV_PRM_DT]), DateDiff("d", Date(), [X65-SCHATS])), 
   Null)

    or simplified

    IIf([REV_PRM_DT] <> #01/01/2055# AND [Green_Date] Is Null, 
   DateDiff("d", Date(), IIf(Not [REV_PRM_DT] Is Null, [REV_PRM_DT], [X65-SCHATS])), 
   Null)

    Is Null is SQL operator. Nz() is VBA function but can be called in Access query. Calling VBA function can slow performance but likely not noticeably.

    IIf([REV_PRM_DT] <> #01/01/2055# AND [Green_Date] Is Null, 
   DateDiff("d", Date(), Nz([REV_PRM_DT], [X65-SCHATS])), 
   Null)

    Because of AND operator, REV_PRM_DT must be <> #01/01/2005# and Green_Date must be Null in order to calculate DateDiff - if you want calc when either case is true, use OR. Maybe this is what you need:

    IIf([REV_PRM_DT] = #01/01/2055# OR Not [Green_Date] Is Null, 
   Null,
   DateDiff("d", Date(), Nz([REV_PRM_DT], [X65-SCHATS])))