Dates: month and day switch

[corneasd]

Hi, I need some advice.

I use an Insert query to add a row to a table, for example:
"Insert Into table( ta_date) Values( " & myDate & &quot", where myDate is a date.

The computer date format and the table field is set to the format "dd/mm/yyyy". The clients prefer to enter dates in this format.

The problem is that when the query is run, a date such as the first of August ends up as the eight of January.

I've checked myDate, before it enters the query it is "01/08/2003", but it always ends up as "08/01/2003" in the table.

I've even explicitly dimensioned myDate as a date, individually setting the month, day and year to the required values, but the problem persists.

The only solution that I've found is opening the table with a select query, and then using recordset.addnew to add a record.

Needless to say, I prefer using the Insert query.

Can anyone offer advice? It will be highly appreciated.

 

[oharab]

Access SQL always wants and expects dates in the American format so:
Insert Into table( ta_date) Values( " & format(myDate,"mm/dd/yyyy&quot & &quot"

should sort it.

hth

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[TonyJollans]

Hi corneasd,

I think date formats cause more grief than almost anything else in Access. It is important to understand that dates are held internally in their own format - it doesn't matter what that format is for the moment. Formatting of dates on output is governed by any explicit format functions you choose to use or, by default, by the system Regional settings, and has no bearing on the interpretation of input dates.

Interpretation of input dates is more complex as it depends on what is interpreting them. It is true, as Ben says, that Access will use the American date format (mm/dd/yy) to resolve any ambiguity in interpreting a date literal as a date, but the real complexity comes from the fact that dates are often interpreted more than once.

Your post doesn't make it clear (to me) where you are entering the SQL. As posted it won't run in a Query and, if used in code, the 'date' will be interpreted as a numeric expression - either (1 divided by 8 divided by 2003) or (1 divided by 8 divided by 3) depending on how it is defined - and will become some time early on 30 December 1899.

It would take too long to go through all the permutations of how myDate is Dimmed and how it is passed to the database - from a form or a prompt or another table - as a string (delimited by quotes) or as a date literal (delimited by hashes) - etc. Can you post back with more specific information - or point out my blind spot

Enjoy,
Tony