US and UK dates confused

[John M]

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04.
as a consequence I can't get the right record selected. How do i ensure all
references to dates are as in UK? My PC is ... I think correctly set up.

This has driven me round the bend!!

Thanks in advance

John M

[Fletcher Arnold]

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:c0*******************@news.demon.co.uk...

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04. as a consequence I can't get the right record selected. How do i ensure all references to dates are as in UK? My PC is ... I think correctly set up.

This has driven me round the bend!!

Thanks in advance

John M

If you are designing queries using the Access GUI, then you can write dates
in dd/mm/yyyy and provided your PC is set up correctly, then these will be
interpreted and adjusted accordingly (have a look at the SQL generated for
your query). However, when you are writing code you are simply supplying a
string, and no attempt is made to convert this based on regional settings.

You could simply format the date when you create the WHERE clause

strWhere = "WHERE MyDate = #" & Format(dteDate, "mm/dd/yyyy") & "#"

or use a function such as DateSerial which is great for returning
unambiguous dates.

Fletcher

[Bas Cost Budde]

John M wrote:

Hi,
I have spent some time trying to sort out a date problem (see mail an hour
or so ago) and am now getting to see what the problem is.

If I present today's date as 07/02/2004 in a search it come up as 02/07/04.
as a consequence I can't get the right record selected. How do i ensure all
references to dates are as in UK? My PC is ... I think correctly set up.

May be; but Access (Jet, actually) uses US date format. Live with that
(I do) and maybe format your dates with format(yourdate, "mm/dd/yyyy")
in the queries.

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

[Lyle Fairfield]

"John M" <jo**@jmawer.demon.co.uk> wrote in
news:c0*******************@news.demon.co.uk:

If I present today's date as 07/02/2004 in a search it come up as
02/07/04. as a consequence I can't get the right record selected. How
do i ensure all references to dates are as in UK? My PC is ... I think
correctly set up.

Access handles dates as UK dates if you write "really stubborn and perverse"
in front of each. If you want a civilized, consistent, widely understandable
approach try standardizing to "yyyy-mm-dd".

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Bas Cost Budde]

Lyle Fairfield wrote:

If you want a civilized, consistent, widely understandable
approach try standardizing to "yyyy-mm-dd".

I recognize ISO!
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl