Date format problem

[tomlander]

In my code, I am trying to assign a date value to "thisDate", when I put in

thisDate = #July 01, 2002#
or
thisDate = #1/7/2002#

Access keeps changing it to

thisDate = #7/1/2002#.

how can I prevent Access from changing the date format?

Thanks for any help.

 

[lameid]

Access is pulling the regional settings found in control panel for it's default display. You can change this default if your locale warrants it. You might also consider using the format function to format the date as you wish or even specify the format in the table.

format (#July 01, 2002#, "dd/mm/yyyy&quot

is equal to
01/07/2002

 

[tomlander]

Hi, lameid:

thanks for your responds.

I have set the regional settings to "dd/mm/yy", but Access still changes the date format to "mm/dd/yy".

The reason I am trying to set the date format is that seems when doing dates comparison, the format will affect the result.

In my table field setting, date format is "dd/mm/yy", so when I do the following:
&quot;SELECT * FROM tblInterestRates WHERE [Date]< #&quot; & thisDate & &quot;# ORDER BY [Date];&quot;

even I have set thisDate = #July 04, 2002#, Access shows it as thisDate = #7/4/2002#. Now, in the above statement, Access will interpret thisDate as April 7, 2002.

Any idea why it does this?

Thanks again.

 

[newfrontiers]

Hi tomlader,

I have been having the same problems due to an international program that needs to handle both dd/mm and mm/dd formats. As Lameid mentioned, try using the Format commmand. Considering your SQL string maybe Format([Date],&quot;dd/mm/yyyy&quot would work.

If this does not help, search the internet for access date issues. When I did this several sites popped-up. I just had my computer reformatted otherwise I would give you the specific links.

regards.

 

[gransbpa]

Hi Tomlader,

What you could try is working with the dateformat dd/mmm/yyyy. This format uses (usually the first three) characters of the month, so month and day order can't get mixed up any more.

succes.

 

[GHolden]

FYI.

I found this out just the other day..

This (using the # identifiers) is always US format:
#1/2/2002# is Month/Day/Year = 2 Jan 2002

Your system is set in a different format.

On your system, it will then get re-formated to Day/Month/Year, as in: 2/1/2002, which still is 2 Jan 2002.

If you need to us the the # identifiers, such as in a SQL statement, then you need to do this in order to make sure that it gets formated correctly:

Format(TheDate, &quot;\#mm\/dd\/yyyy\#&quot

There are two ways to write error-free programs; only the third one works.