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.