Date Format in a Query

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi again all...

I have an issue with the date format in a query. The query is based on
fieled, "Date created", from a table whoes format is "Long Date"
Day-Month-Year and the time (this because the default value is Now().

Days: IIf([Case Status]="Open" Or [Case Status]="Review",[Today]-[Date
Created])

The result of the above should be amount of days...but unfortunatly i
recieve decimals...i.e should be 3 days..but returned value is 0.369896
etc....."Date Created" should only contain the date value in the query and
not the time..

hmm...

Thanks again for a great place!
 
Try DateDiff():

Days: IIf([Case Status] IN ("Open", "Review"),
DateDiff("d", [Today], [Date Created]), Null)
 
Use the DateDiff function. It will return the number of days as an integer.
But, it will round the number of days. For example, if Now() is 6/4/2009
7:30:49 AM and Date Created is 5/4/2009 8:33:33 AM, using your formula, the
return would be 30.9564351851877. The DateDiff function would round it to 31.



Days: IIf([Case Status]="Open" Or [Case
Status]="Review",DateDiff("d",[Today],[Date Created]))
 
Use the DateDiff function to calculate the number of days between the
two fields.

DateDiff("d",[Today],[Date Created])

Also if Today is supposed to be the current date, then you should be
using the Date() function.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Peter said:
I have an issue with the date format in a query. The query is based on
fieled, "Date created", from a table whoes format is "Long Date"
Day-Month-Year and the time (this because the default value is Now().

Days: IIf([Case Status]="Open" Or [Case Status]="Review",[Today]-[Date
Created])

The result of the above should be amount of days...but unfortunatly i
recieve decimals...i.e should be 3 days..but returned value is 0.369896
etc....."Date Created" should only contain the date value in the query and
not the time..
Click to expand...


Adding/subtract date/time values is not a good thing to do.
Instead you should use the built in date and time functions.
In this case you can use the DateDiff function:

DateDiff("d", [Date Created], Date())
 
Thank you every one, i am very greatfull for your answers, indeed it helped.
One thing viser today...thanks again...DatDiff...reminds me of the Datedif in
excel...thanks!
 
Back
Top