If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Date Format in a Query
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! |
#3
|
|||
|
|||
Date Format in a Query
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! |
#4
|
|||
|
|||
Date Format in a Query
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! |
#5
|
|||
|
|||
Date Format in a Query
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] |
#6
|
|||
|
|||
Date Format in a Query
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! |
Thread Tools | |
Display Modes | |
|
|