Handling DateDiff() with blank date fields

[7 of 9]

OK here is my code:

 
Select Case DateDiff("D", Shipdt, Date)
    Case Is > 90
        WTAge = "GT-90"
    Case Is > 60
        WTAge = "61-90"
    Case Is > 30
        WTAge = "31-60"
    Case Is < 31
        WTAge = "LT-31"
End Select

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?

[puppydogbuddy]

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)

[missinglinq]

This should handle having nothing in the field, and something similar should probably be included in all Select Case constructs, to handle the unexpected:

Select Case DateDiff("D", Shipdt, Date)
    Case Is > 90
        WTAge = "GT-90"
    Case Is > 60
        WTAge = "61-90"
    Case Is > 30
        WTAge = "31-60"
    Case Is < 31
        WTAge = "LT-31"
    [B]Case Else
        WTAge = ""
[/B] 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