Date problem (need to use DD MM YYYY instead of MM DD YYYY (Australia)

[Philip]

Hello All
I've not touched Access for several years now, and I've forgotten what you
have to do to get around the SQL Date problem when the region is set to
Australia (or any country that uses DD/MM/YYYY)
I need to specify a date range in a sales report.
Currently, I have a report with the source as the sales table.
I have a filter defining the date range from the form that calls the report
ie.
(([PaidDate] >=#" & <date from form> & "#) AND ([PaidDate] <= #" & <date
from form> & "#))

if the above line evaluates to something like (remember I have used
DD/MM/YYYY)
(([PaidDate] >=#31/01/2004#) AND ([PaidDate] <= #29/02/2004#))
then all is OK as access can see that the date is obviously specified in
DD/MM/YYYY
but if is evaluates to
(([PaidDate] >=#01/02/2004#) AND ([PaidDate] <= #29/02/2004#))
then Access looses the plot, and gives my a much wider date range then I
wanted.

I remember that there is some way around this, but for the life of me, I
cant remember what it is!

Can someone refresh my memory?

Thanks. Philip Middleton

 

[Allen Browne]

Hi Philip

In your interface, you can use the Aussie/British format of dd/mm/yyyy by
defining that in the Windows Control Panel under Regional Settings.

However, in a SQL clause, you must use the American format mm/dd/yyyy. I
guess if they did not do that, a piece of code that we wrote here would not
work the same in other places on the planet. (BTW, this applies to the
Criteria argument of DLookup() etc - anywhere that Access is calling JET.)

We have found 3 cases where Access can misunderstand our date format.
Details and workarounds in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Philip]

Thanks Allen. Checking it out now.

Hi Philip

In your interface, you can use the Aussie/British format of dd/mm/yyyy by
defining that in the Windows Control Panel under Regional Settings.

However, in a SQL clause, you must use the American format mm/dd/yyyy. I
guess if they did not do that, a piece of code that we wrote here would not
work the same in other places on the planet. (BTW, this applies to the
Criteria argument of DLookup() etc - anywhere that Access is calling JET.)

We have found 3 cases where Access can misunderstand our date format.
Details and workarounds in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello All
I've not touched Access for several years now, and I've forgotten what you
have to do to get around the SQL Date problem when the region is set to
Australia (or any country that uses DD/MM/YYYY)
I need to specify a date range in a sales report.
Currently, I have a report with the source as the sales table.
I have a filter defining the date range from the form that calls the report
ie.
(([PaidDate] >=#" & <date from form> & "#) AND ([PaidDate] <= #" & <date
from form> & "#))

if the above line evaluates to something like (remember I have used
DD/MM/YYYY)
(([PaidDate] >=#31/01/2004#) AND ([PaidDate] <= #29/02/2004#))
then all is OK as access can see that the date is obviously specified in
DD/MM/YYYY
but if is evaluates to
(([PaidDate] >=#01/02/2004#) AND ([PaidDate] <= #29/02/2004#))
then Access looses the plot, and gives my a much wider date range then I
wanted.

I remember that there is some way around this, but for the life of me, I
cant remember what it is!

Can someone refresh my memory?

Thanks. Philip Middleton

 

[Philip]

Bingo! The Format command.
Format(Me.StartDate, "mm\/dd\/yyyy")
Thanks Allen.

Thanks Allen. Checking it out now.

Hi Philip

In your interface, you can use the Aussie/British format of dd/mm/yyyy by
defining that in the Windows Control Panel under Regional Settings.

However, in a SQL clause, you must use the American format mm/dd/yyyy. I
guess if they did not do that, a piece of code that we wrote here would not
work the same in other places on the planet. (BTW, this applies to the
Criteria argument of DLookup() etc - anywhere that Access is calling JET.)

We have found 3 cases where Access can misunderstand our date format.
Details and workarounds in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello All
I've not touched Access for several years now, and I've forgotten what you
have to do to get around the SQL Date problem when the region is set to
Australia (or any country that uses DD/MM/YYYY)
I need to specify a date range in a sales report.
Currently, I have a report with the source as the sales table.
I have a filter defining the date range from the form that calls the report
ie.
(([PaidDate] >=#" & <date from form> & "#) AND ([PaidDate] <= #" & <date
from form> & "#))

if the above line evaluates to something like (remember I have used
DD/MM/YYYY)
(([PaidDate] >=#31/01/2004#) AND ([PaidDate] <= #29/02/2004#))
then all is OK as access can see that the date is obviously specified in
DD/MM/YYYY
but if is evaluates to
(([PaidDate] >=#01/02/2004#) AND ([PaidDate] <= #29/02/2004#))
then Access looses the plot, and gives my a much wider date range then I
wanted.

I remember that there is some way around this, but for the life of me, I
cant remember what it is!

Can someone refresh my memory?

Thanks. Philip Middleton