Access Date

[SirDrinksalot]

Hi all,

I have a database from which I build a query to populate a table with data, and one of the fields is a user selected date from a calender control. The query looks something like this:

SELECT 
SampleSite.SiteID, 
Format(#06-09-2006 11:42:19#,"dd/mm/yyyy") AS D_Date, 
"" AS UserID, 
SampleSite.SampleSite AS SourceID, 
0 AS Depth, 
0 AS Temp, 
0 AS pH, 
0 AS ElectricalConductivity, 
0 AS DissolvedOxygen, SampleSite.SiteID+"."+SampleSite.SampleSite+"."+SampleSite.Type AS SampleRef, 
SampleSite.Suite AS Suite, 
"" AS Comments 
FROM SampleSite

Unforunately, for the date shown above, when inserted into the table it comes out as 09/06/2006, and subsequent queries searching against date do not find this record. I understand that access only uses the US date format mm/dd/yyyy, so the question is how do i stop this from happening? I noticed that if i put a date of 13/09/2006, the date is inserted correctly, but dates with a day value of less than 12 get 'switched'.

Arrgghhhh!!

Dan

 

[ColinEssex]

Format the date field in the table to "dd/mm/yy"

Access stores dates as numbers (of days) since January 1st 1900. You use the funny American format in the VB code then convert it to the proper way in the table.

Also check your regional settings to make sure they are UK not US versions.

Col

 

[statsman]

Another spendid example of why, when you join this forum you should enter a location, even if it's only the country.

 

[gemma-the-husky]

Just a thought

to avoid date problems with US/UK transpositions, when storing the date, or manipulating it, try format(date,"Long Date") which seems to avoid any ambiguities for date handling.

 

[Mile-O]

I tended to use Format([Field], "mm/dd/yyyy") when querying my UK dates in tables.