General discussion

Locked

Access Dates Problem

By munrrob ·
I am running Access 97 Office SR-2b on Windows NT 4.0 Workstation SP6a.

I have the following code in a module:

sql = "SELECT *" _
& " FROM tblWaitingTimes" _
& " WHERE tblWaitingTimes![wtDate] = #" & oldDate & "#;"

where oldDate is a date in uk format of dd/mm/yyyy.

When processing this the statement shows the date in dd/mm/yyyy but the moves the day and month values around.

The system is set to UK date format with dd/mm/yyyy as the preferred choice.

I know the date is being transposed as I tried placing this in a query and running it.

How can I stop the system transposing the day and month values?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Dates Problem

by DKlippert In reply to Access Dates Problem

SQL assumes the "American" form.
You can use the Format function.
look at:
www.mvps.org/access/datetime/date0005.htm

Collapse -

Access Dates Problem

by munrrob In reply to Access Dates Problem

Poster rated this answer

Collapse -

Access Dates Problem

by john_wills In reply to Access Dates Problem

If the code is in a module, why are you using a text date at all? Manipulation of dates in VBA should generally be done in the Date type, so that format appears only at transput. My PC is set for ISO date format, whereas most of my users choose half-backwards, but the software does not need to be changed when they accept it from me. Indeed, when the database is on a server there may be several users with different date formats using it. If you have a text for the date you can present it to SQL as in your example within the DateValue function, which will cleverly figure out what date format is intended and, so far as I remember, use the Windows regional setting where there is ambiguity.

Collapse -

Access Dates Problem

by munrrob In reply to Access Dates Problem

Poster rated this answer

Collapse -

Access Dates Problem

by munrrob In reply to Access Dates Problem

The dates are in text format as they are generated as being the first of the current month. As the code will run automatically when a user logs in I only use this if an entry for the first of the month does not exist. I then use CDate to change the string to a date for passing to the SQL statement.

My problem seems to be that the SQL statement will read the date stored in the table in UK format but will transpose the date I submit to it into US date format. I tried the format function to change this but it made no difference.

Collapse -

Access Dates Problem

by munrrob In reply to Access Dates Problem

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums