Date Format in a Query

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" wrote in message
...
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!

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]))
--
Dave Hargis, Microsoft Access MVP


"Peter" wrote:

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!

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 wrote:
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!

Peter wrote:
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..


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())

--
Marsh
MVP [MS Access]

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!

"Peter" wrote:

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!