• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

DateDIFF with IsNull (1 Viewer)

Waka0212

New member
Local time
Today, 18:10
Joined
Oct 30, 2019
Messages
14
I have an expression that is currently working in the format of x year(s), x month(s), x day(s), but if the result does not have a value in in each category I want it NOT to display that group. Such as if the return is 5 months and 16 days, I want it not to display the years segment. Below is the code I'm using. I've tried adding ISNULL(xxxx) at the beginning but its giving me an error. Any help would be aprreciated!

TIR Date Calc: DateDiff("m",[TIR Date],Date())\12 & "year(s), " & DateDiff("m",[TIR Date],Date()) Mod 12 & "month(s), " & DateDiff("d",[TIR Date],Date()) Mod 12 & "day(s)"
 

plog

Banishment Pending
Local time
Today, 17:10
Joined
May 11, 2011
Messages
10,083
but if the result does not have a value in in each category

There's a difference between 0 and NULL. There's no possible way for just one category to be NULL and not the other 2 (or vice versa).

Now if you want to omit 0 values then you should test for that instead of testing for NULLS:

Iif(DateDiff("m",[TIR Date],Date())\12 > 0, DateDiff("m",[TIR Date],Date())\12 & "year(s) & "year(s) & ...

Lastly, are you sure the Days portion is correct? Seems odd you are doing MOD 12 on it. This portion is going to cause you headaches and to revaluate the whole approach.
 

Waka0212

New member
Local time
Today, 18:10
Joined
Oct 30, 2019
Messages
14
There's a difference between 0 and NULL. There's no possible way for just one category to be NULL and not the other 2 (or vice versa).

Now if you want to omit 0 values then you should test for that instead of testing for NULLS:

Iif(DateDiff("m",[TIR Date],Date())\12 > 0, DateDiff("m",[TIR Date],Date())\12 & "year(s) & "year(s) & ...

Lastly, are you sure the Days portion is correct? Seems odd you are doing MOD 12 on it. This portion is going to cause you headaches and to revaluate the whole approach.
Honestly, I don't know about majority of coding. I typed what I wanted in Google and found this expression and made it work. Or atleast thought I did. If there is a better way to return a year(s), month(s), days(s) result, I am totally open to suggestions! Please and thank you! And when I get back to my computer, I will try the supplied fix. Thanks!
 

plog

Banishment Pending
Local time
Today, 17:10
Joined
May 11, 2011
Messages
10,083
Your big problem is that time isn't metric. You can't just convert units and have things work. "Year" is a poorly defined term (less than 75% of them have 365 days), "Month" is poorly defined (they can have anywhere between 28 and 31 days). You are going to end up with some weird results.

To reduce (not eliminate) some of the weird results I suggest you work only in days, not months and days. Get the total difference in days, then do math on that until you have no days remaining. I would do this in a function and not in one expression as you currently have. Calculate the days difference between your 2 dates in days, then extract out and then subtract however many years are in there, then however many months, then use the remainder as the days left. Again, that only reduces the weird results, not eliminates them.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 19, 2002
Messages
29,803
Access supports two concatenation operators. The standard ampersand (&) and it also overloads the plus (+) to concatenate when one of the operands is not numeric. In addition, the two operators handle null differently. The & treats null as a ZLS and so it concatenates "nothing". However, the + respects the concept of null and if one or both of the operands is null, the expression returns null. So:

2 & " Months" = "2 Months"
Null & " Months" = " Months"

BUT

Null + " Months" = ""

Try replacing the relevant &'s with +'s

TIR Date Calc: DateDiff("m",[TIR Date],Date())\12 + " year(s), " & DateDiff("m",[TIR Date],Date()) Mod 12 + " month(s), " & DateDiff("d",[TIR Date],Date()) Mod 12 + " day(s)"

PS, One of the things to avoid in the future is using embedded spaces or special characters in your object names. NO programming environment supports embedded spaces in variable names and most do not support special characters (except for _ or in some cases - ) so to compensate, Access forces you to enclose all offending names with square brackets []. these are just extra characters to type AND they make expressions that include both [] and () all that much more confusing to read.
 

Waka0212

New member
Local time
Today, 18:10
Joined
Oct 30, 2019
Messages
14
Sorry for the late reply. I will start trying these ASAP and update. Thanks for all the replies! And @Gasman, lesson learned for code snippets. Thank you!
 

Users who are viewing this thread

Top Bottom