Access 2007 - DateDiff Problem

[burrelly]

[Resolved]Access 2007 - DateDiff Problem

I am trying to calculate the number of days between 2 dates. The dates are formated in uk short format eg 18/02/2008 when i do date diff on the following dates for example it gives the following:

01/09/2008 to 07/09/2008 = 7 day but when access does the calculation it shows 182 days.

I am using the following:

No of Days: DateDiff ("d",Now(),[date1].[date2])

Thanks

 

[Rabbie]

It looks like Datediff thinks your dates are in US format mm/dd/yyyy. The difference of 182 is correct for difference between 9th January to 9th July.

 

[burrelly]

Yeah any idea how I can do this then? The date fields are formatted correctly for the UK but the function seems to think it is US.

 

[Rabbie]

I have checked parameters for Datediff and I am not sure what you mean by [date1].[date2]).

If you want the number of days between date1 and date2 then use datediff("d",date1,date2)

EDIT.

Also Date1 and Date2 should be Date/Time fields not string fields for datediff to work correctly

 

[burrelly]

Dont worry I have got it sorted. Was a problem with the regional setting on the PC i was using.

Thanks Anyways

 

[likelike2045]

i have a table , [order]![date]
I want to calculate which client didn't place order over 30 days

=Datediff("d",[order]![date],now())>30

But this didn't show any result in the query. Who can tell me what is the problem?

Thank you

 

[likelike2045]

i fixed it.

 

[clawgee]

How did you fix it? I am having the same problem using a calculated column within my table, DateDiff("m",[Year License Acquired],Date())/12