Software

Ask something
Discussion Locked

Access Dates Problem

0 helpful
6 replies

by munrrob

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

6 replies

by john_wills

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.

by munrrob

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.

This discussion is closed to new replies.