Help on Date (1 Viewer)

[racdata]

In my table I have a field named Lic_Date (Licence Date). This licence is valid for 1 year. On the query I ad a Lic_Exp field - Lic_Exp[Lic_Date]+365.25)
Format for both is dd/mmm/yyyy
Lic_Date - 15/Oct/2005
LicExp - 15/Oct/2006

In the Lic_Exp field I enter the criteria - Between [DateFrom] and [DateTo]

Entering for example 15/10/2006 to 31/10/2006
I then get all the entries in the query - dates before 15/10/2006 and dates after 31/10/2006. There are only 2 entries within these dates - 19-Oct-2006 and 21-Oct-2006

Can you help please?

 

[EMP]

It sounds like Access is matching [Lic_Exp] and the criteria as if they were text strings rather than dates. Try using the DateAdd() function instead of adding 365.25 days.

Lic_Exp: DateAdd("yyyy", 1, [Lic_Date])


Note:
Adding 365.25 days to a Date is actually adding 365 days and 6 hours to it.

^