Aussie Date Formats in queries (ddmmyyyy) <> (mmddyyyy)

[Jinxie]

Hello all,

I have a basic form that writes an SQL that is run when a 'go' button
gets clicked. On this form I have a combo box that is populated by a
SELECT DISTINCT query that returns possible dates from the dataset.

When the code runs the query is generated to a variable and then run
eg.

SELECT
*
FROM
Table A
WHERE
Report_Date = #dd/mm/yyyy#;

For dates where the days component (dd) >= 13 (eg 31/12/2005 - 31 Dec
2005) this works fine as the query interprets the date as dd/mm/yyyy.

For dates where the days component (dd) <= 12 (eg 01/12/2005 - 01 Dec
2005) the query interprets the date as mm/dd/yyyy (eg 12 Jan 2005).

Interestingly, if I copy the query across and put it into a query the
same thing happens.

Any Ideas people? I'm sure this will be something simple and I'm going
to kick myself.

Cheers, Jinxie.

--
JinxiePosted from - http://www.officehelp.in

[Jerry Whittle]

SQL is an ANSI standards and the A doesn't stand for Aussie! It stands for,
ah you know what. Yep it's the strange USA way of doing dates.

If people are entering the date in a form before running the query it mighe
be best to have seperate Day, Month, and Year fields to stop the confusion.
You could then properly combine these three fields into a date in the
mm/dd/yyyy format.

BTW: I've seen Aussie Pink Floyd twice in concert and they are nearly as
good at the originals.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

[Van T. Dinh]

Regardless of your Regional Settings, literal date values in SQL String must
be in the U.S. format "mm/dd/yyyy" or an internationally unambiguous format
like "yyyy-mm-dd" and enclosed in hashes (#).

--
HTH
Van T. Dinh
MVP (Access)

"Jinxie" <Jinxie...@NoSpamPleaze.com> wrote in message
news:Jinxie...@NoSpamPleaze.com...

[Allen Browne]

Jinxie, to add to Jerry's advice, you might like to read:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The advice in that article comes from 13 years experience using Access with
Australian dates, and describes the 3 cases where Access is likely to get
your intentions wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Whittle" <JerryW...@discussions.microsoft.com> wrote in message
news:C449487B-A287-4557...@microsoft.com...

[John Waller]

or an internationally unambiguous format
> like "yyyy-mm-dd"

Isn't "dd/mm/yyyy" an internationally unambiguous format?

or is dd/mm too similar to mm/dd (e.g. 01/02 or 02/01)?

--
Regards

John Waller

[TC]

John Waller wrote:

> Isn't "dd/mm/yyyy" an internationally unambiguous format?

Nope. Which is the day, & which is the month? The answer depends on the
country you live in. In Australia, 06/09/2006 would be 06 September
2005. In the US, it would be June 09, 2006.

HTH,
TC

[david@epsomdotcomdotau]

> The advice in that article comes from 13 years experience using Access
with
> Australian dates, and describes the 3 cases where Access is likely to get
> your intentions wrong.

But it includes the advice that "In all versions of Access (including the
16-bit
versions), JET SQL clauses require dates in American format"

After several years of trying different things, we settled on #yyyy-mm-dd#,
because it is clear, unambiguous, and similar to the ODBC canonical form.

Other formats which sort of work with Jet SQL are
format(date,"\#dd mmm yyyy\#")
which works only in english locales (like -au and -us), and
cdbl(date)
which causes off by one errors when using SQL Server.

A disadvantage of 'American' format that you frequently
can't tell by looking at a date if it is in American or British
format.

(david)

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:Of2KXbaE...@TK2MSFTNGP10.phx.gbl...

[Allen Browne]

Hi David, and thank you for your comment.

Have replaced the statement:
... JET SQL requires dates in American format
with:
... JET SQL expects dates in American format

It does of course spin the dates around if you give it a non-ambiguous value
to interpret, so "require" was inaccurate.

To demonstrate that JET and VBA expect the date in the American format,
enter an ambiguous date, e.g.:
UPDATE Table1 SET Date1 = #03/04/05#;
or in the Immediate Window:
? #03/04/05#

I also have a personal preference for using the yyyy mm dd format for dates,
as it makes the most sense, sorts correctly even if the data engine
misinterprets it as text, and is the database standard. However, it is not
the native format for JET or VBA, so I recommend suppling dates in the
format that JET and VBA expect them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<david@epsomdotcomdotau> wrote in message
news:OP8a2ld...@TK2MSFTNGP12.phx.gbl...

[Bob Hughes]

"TC" <gg.20.k...@spamgourmet.com> wrote in
news:1136437132.0...@g14g2000cwa.googlegroups.com:

I ran into this problem a couple of years ago when a company decided to
change the system regional setting to dd/mm/yyyy

I now use this function or constant for all date requests

Public Const pDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l" 'for date format in
sql searches

Public Function fFixDate(dt As Date) As String
'this is needed to handle all region date formats
fFixDate = Format$(dt, pDateFmt )
End Function

I have not had a complaint since. (Maybe I should not say that)

Bob

--
TO reply via e-mail, change the xxx in the address to bob_

[david@epsomdotcomdotau]

On my tests, the native format for dates was cdbl(date).
At least, back when there was a measurable difference,
that was much faster than any of the #...# formats.

However, I take your point that #06/05/04# will be
interpreted as June 5, 2004, not 2006 May 4.

(david)

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message

news:%230OsVzd...@TK2MSFTNGP10.phx.gbl...

[Jinxie]

Thank you to you all for your help. With it I have managed to get around
my problems. You have also give me some new ideas which is great :)

[TC]

I've done the same thing. It's a fine way to avoid the problem.

TC [MVP Access]