Date Diff in a query or form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a date diff expression in a form asking for the days aging between when a customer calls in a complaint and when its resolved. I ask for the date diff between the fields [date],[close date] . But if my close date is blank then I dont get a result --if I change my expression to [date],now then I get the days aging between the date open and current date even if the case is closed. Can I write a better expression to datediff between open date and closed date or current date if closed date is null or should I do something completely different?
 
I have a date diff expression in a form asking for the days aging between when a customer calls in a complaint and when its resolved. I ask for the date diff between the fields [date],[close date] . But if my close date is blank then I dont get a result --if I change my expression to [date],now then I get the days aging between the date open and current date even if the case is closed. Can I write a better expression to datediff between open date and closed date or current date if closed date is null or should I do something completely different?
Click to expand...

First off, change the name of the field Date. There is a builtin
Access function Date() and if you have a field with the same name,
Access WILL get confused. Let's say it's changed to ComplaintDate.

You can then use the NZ() function to select today's date if [Close
Date] is NULL:

DateDiff("d", [ComplaintDate], NZ([Close Date], Date()))
 
I have a date diff expression in a form asking for the days aging
between when a customer calls in a complaint and when its resolved.
I ask for the date diff between the fields [date],[close date] .
But if my close date is blank then I dont get a result --if I
change my expression to [date],now then I get the days aging
between the date open and current date even if the case is closed.
Can I write a better expression to datediff between open date and
closed date or current date if closed date is null or should I do
something completely different?
Click to expand...

2 things:
1) If your complaint date field is really named [date], I would
strongly suggest you change it to something else, perhaps CallDate or
dteDate, etc.
Date is a reserved word in Access/VBA.

Read the appropriate Microsoft KnowledgeBase Article:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) To answer your question ....

How about: DateDiff("d",[CallDate],Nz([ResolvedDate],Date()))
 
THANK YOU SOOO MUCH--I KNEW THE ANSWER HAD TO BE EASY BUT COULD NOT FIND IT IN/FROM MANY BOOKS,REFERENCES AND FRIENDS---
 
Back
Top