Stored date is not being recognised

Joined
Nov 2, 2012
Messages
4
Reaction score
0
I have an Access 2007 database that generates a report based on todays date.
Firstly all the records on the database where the date is NULL is updated with the current system date. A query is then run that uses the system date as the criteria. No records are returned. This worked last week for the 26th of October but not for the 1st of November.
Looking at the table the dates for the 26th of October are displayed as 26/10/2012 and for the 1st of November as 11/01/2012. Now I know that dates are stored as a number and when displaying you need to format to display the correct regional format BUT all I'm doing is storing using Date and retrieving using Date and it no longer works. I've checked regional settings and I'm using dd/mm/yyy.

The code I'm using is
UPDATE [Kits] Set [Shipdate] = #" & Date & "# WHERE isnull([ShipDate])
The query has Date() against ShipDate
When looking at the table Shipdate is 11/01/2012

I've also tried
UPDATE [Kits] Set [Shipdate] = #" & Format(Date, "dd/mm/yyyy") & "# WHERE isnull([ShipDate])


I am using Access 2007 and running on Windows XP professional some with SP2 and some with SP3.

Any help appreciated
 
Joined
Nov 2, 2012
Messages
4
Reaction score
0
Right - more info.

My update above is storing the julian date of 40950 which is the 11th of Feb I believe

Oh and just to add this was run on 2nd of Nov
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top