Strange problem with Dates in Access

[dlarkin77]

Hi,

I am having a very strange problem working with Date fields in MS Access. Basically I have two date fields. I am trying to set one date to be three years after the other ie: 1 January 2005 in the first field, 1 January 2008 in the second field.

I am using the DateAdd function to do this. The code I use is:

' this is inside a loop. loops through each item in a listbox with 2 items
dtPurchase = lstPurchaseDate.List(lCounter)
dtWarranty = CDate(DateAdd("yyyy", 3, dtPurchase))
sUpdateSQL = "Update tblDate Set WarrantyExpirationDate = #" & dtWarranty & "# Where PurchaseDate = #" & dtPurchase & "#"

The SQL generated is as follows:

Update tblDate Set WarrantyExpirationDate = #12/05/2004# Where PurchaseDate = #12/05/2001# 'doesn't work

Update tblDate Set WarrantyExpirationDate = #25/04/2003# Where PurchaseDate = #25/04/2000# 'does work

The first time round WarrantyExpirationDate isn't modified at all. The second time round it is modified correctly.

So, I don't think it's a problem with the code I use (but maybe I'm wrong). Has anyone ever had a similar problem? If so, any advice or suggestions?

Thanks very much,

dlarkin77

Edit: The following SQL also works :

 Update tblDate Set WarrantyExpirationDate = #13/05/2004# Where PurchaseDate = #13/05/2001#

The field isn't updated if the day of the month is less than 13 ie: 1 - 12 don't work.

[HanneSThEGreaT]

I'm wondering if it might be your computer's date format ..... or some of your date fields were stored in a different format........

[rahul.kul]

Hi

try this and tell me...!

 
dtPurchase = lstPurchaseDate.List(lCounter)
dtWarranty = CDate(DateAdd("yyyy", 3, dtPurchase))
sUpdateSQL = "Update tblDate Set WarrantyExpirationDate = #" & Format(dtWarranty,"dd-MMM-yyyy") & "# Where PurchaseDate = #" & Format(dtPurchase,"dd-MMM-yyyy") & "#"

Try to use Month as MMM in your Access Data Types or where-ever you used.. so that the Month-Day confliction may not be exist in your query.

Regards@

[dlarkin77]

That's brilliant rahul.kul! It worked perfectly . Would you mind explaining to me what the problem was?

Thanks very much,

dlarkin77

[HanneSThEGreaT]

Hi

try this and tell me...!

 
dtPurchase = lstPurchaseDate.List(lCounter)
dtWarranty = CDate(DateAdd("yyyy", 3, dtPurchase))
sUpdateSQL = "Update tblDate Set WarrantyExpirationDate = #" & Format(dtWarranty,"dd-MMM-yyyy") & "# Where PurchaseDate = #" & Format(dtPurchase,"dd-MMM-yyyy") & "#"

Try to use Month as MMM in your Access Data Types or where-ever you used.. so that the Month-Day confliction may not be exist in your query.

Regards@

That is brilliant Rahul!
As I've suspected, some dates were stored in the wrong format!

[dlarkin77]

As I've suspected, some dates were stored in the wrong format!

I'm kinda confused! Both date fields are formatted as Short Date (ie: 19/06/2005). What actually happened when I changed the "MM" to "MMM"?

Thanks,

dlarkin77

[rahul.kul]

Dear all...

It is a tiny bug in MS-Access, that whenever dates are less than 13 (= to 12) it confused between dates and Month.. so we have to use "MMM" and applied Format fucntion to display the stored Date Values so that it easily distinguiish the Date and Months differently..

You know... i found it with my experiance doing my projects.. it was also surprsing to me.. but it is true..

As you will apply.. "MMM" ..and you problem will be far away from you..

so thanks for all !

and yes... Enjoy the Coding !! ;-)

[dlarkin77]

Thanks very much rahul.kul and HanneSThEGreaT.

dlarkin77