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
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