By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Handling DateDiff() with blank date fields

7 of 9
P: 23
OK here is my code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select Case DateDiff("D", Shipdt, Date)
  3.     Case Is > 90
  4.         WTAge = "GT-90"
  5.     Case Is > 60
  6.         WTAge = "61-90"
  7.     Case Is > 30
  8.         WTAge = "31-60"
  9.     Case Is < 31
  10.         WTAge = "LT-31"
  11. End Select
  12.  
  13.  
Im using the code to create an aging report for orders. the code tells me if the order shipped > 90, 61-90, 31-60, or >31 days ago but what if the order has not shipped. In that case that field would be blank and would cause an error. How do I handle empty date fields?
Jan 22 '09 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
Dates in date fields are stored as numbers, so the nz function is used to convert nulls to 0 as shown.

nz([Datefield], 0)

You should then add a case statement for the value of 0.
Case Is 0
Exit Select (or maybe Exit Sub)
Jan 22 '09 #2

missinglinq
Expert 2.5K+
P: 3,532
This should handle having nothing in the field, and something similar should probably be included in all Select Case constructs, to handle the unexpected:

Expand|Select|Wrap|Line Numbers
  1. Select Case DateDiff("D", Shipdt, Date)
  2.     Case Is > 90
  3.         WTAge = "GT-90"
  4.     Case Is > 60
  5.         WTAge = "61-90"
  6.     Case Is > 30
  7.         WTAge = "31-60"
  8.     Case Is < 31
  9.         WTAge = "LT-31"
  10.     Case Else
  11.         WTAge = ""
  12.  End Select
And please refrain from using an existing thread to post a separate question. Your question about DateDiff() has nothing to do with the original thread. Doing this is known as "thread hijacking" and against the rules, both here and on most technical forums.

I've moved it to its own thread.

Linq ;0)>

Moderator
Jan 22 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.