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

  • Thread starter Thread starter Philip
  • Start date Start date
P

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
 
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
 
Thanks Allen. Checking it out now.

Allen Browne said:
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.

Philip said:
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
Click to expand...
Click to expand...
 
Bingo! The Format command.
Format(Me.StartDate, "mm\/dd\/yyyy")
Thanks Allen.

Philip said:
Thanks Allen. Checking it out now.

Allen Browne said:
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.

Philip said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top