Set Computers Date Format

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Thanks for taking the time to read my question.

I am wondering if it is possible to set a computers
default Date format (found in regional settings) from
Access.

I need this because a user has their format set up
as "M/d/yyyy" and it seems to make access not work
properly (see other message posted earlier under queries).

If I change their computer settings and open Access it
works perfectly. (I use Format(item to format,"M/d/yyyy")
and format(item to format,"dd-MMM-yyyy") depending on the
computer settings.) Format(item,"M/d/yyyy") doesn't work
in all cases. The other one does.

Thanks again,

Brad
 
Thanks for taking the time to read my question.

I am wondering if it is possible to set a computers
default Date format (found in regional settings) from
Access.

I need this because a user has their format set up
as "M/d/yyyy" and it seems to make access not work
properly (see other message posted earlier under queries).

If I change their computer settings and open Access it
works perfectly. (I use Format(item to format,"M/d/yyyy")
and format(item to format,"dd-MMM-yyyy") depending on the
computer settings.) Format(item,"M/d/yyyy") doesn't work
in all cases. The other one does.
Click to expand...

Many users would be justifiably VERY ANGRY if your application messed
up their settings. It can be done, but a) it's not necessary and b)
generally a bad idea! How would you like it if you ran a program and
suddenly all your Word documents and Excel spreadsheets changed their
contents and appearance?

Format(item, "M/d/yyyy") should work in every case (for what you're
asking it to do - generate a String value like "7/15/2004"). If you
want to use dates input - in whatever format - as a criterion in a
query, you can set the Criterion to

Format([Enter date:], "mm\/dd\/yyyy")

and it should convert the date using the user's preferred settings
into a form which will search the database correctly.
 
I change the settings back when I exit the database. The
problem is while they have the database open all other
programs will be affected... I know, bad solution.

It is the only thing that has worked so far. The query
doesn't work if the Regional Settings is set
to "M/d/yyyy" and I use format(Now(),"M/d/yyyy"), but if
the Regional Settings are set to "dd-MMM-yyyy" and I use
format(Now(),"dd-MMM-yyyy") it works!



So just so I understand...

I can use Format(item,"mm\/ddd\/yyyy") and that will work
for Regional settings set to all of the below?

"dd-MMM-yyyy"
"M-d-yyyy"
"M/d/yyyy"


Brad

-----Original Message-----
Thanks for taking the time to read my question.

I am wondering if it is possible to set a computers
default Date format (found in regional settings) from
Access.

I need this because a user has their format set up
as "M/d/yyyy" and it seems to make access not work
properly (see other message posted earlier under queries).

If I change their computer settings and open Access it
works perfectly. (I use Format(item to format,"M/d/yyyy")
and format(item to format,"dd-MMM-yyyy") depending on the
computer settings.) Format(item,"M/d/yyyy") doesn't work
in all cases. The other one does.
Click to expand...

Many users would be justifiably VERY ANGRY if your application messed
up their settings. It can be done, but a) it's not necessary and b)
generally a bad idea! How would you like it if you ran a program and
suddenly all your Word documents and Excel spreadsheets changed their
contents and appearance?

Format(item, "M/d/yyyy") should work in every case (for what you're
asking it to do - generate a String value like "7/15/2004"). If you
want to use dates input - in whatever format - as a criterion in a
query, you can set the Criterion to

Format([Enter date:], "mm\/dd\/yyyy")

and it should convert the date using the user's preferred settings
into a form which will search the database correctly.


.
Click to expand...
 
What you're describing really doesn't make any sense.

How are you calling the query? What's the actual SQL you're using?

How are you getting the dates as well?

You may find it informative to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have at http://members.rogers.com/douglas.j.steele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brad said:
I change the settings back when I exit the database. The
problem is while they have the database open all other
programs will be affected... I know, bad solution.

It is the only thing that has worked so far. The query
doesn't work if the Regional Settings is set
to "M/d/yyyy" and I use format(Now(),"M/d/yyyy"), but if
the Regional Settings are set to "dd-MMM-yyyy" and I use
format(Now(),"dd-MMM-yyyy") it works!



So just so I understand...

I can use Format(item,"mm\/ddd\/yyyy") and that will work
for Regional settings set to all of the below?

"dd-MMM-yyyy"
"M-d-yyyy"
"M/d/yyyy"


Brad

-----Original Message-----
Thanks for taking the time to read my question.

I am wondering if it is possible to set a computers
default Date format (found in regional settings) from
Access.

I need this because a user has their format set up
as "M/d/yyyy" and it seems to make access not work
properly (see other message posted earlier under queries).

If I change their computer settings and open Access it
works perfectly. (I use Format(item to format,"M/d/yyyy")
and format(item to format,"dd-MMM-yyyy") depending on the
computer settings.) Format(item,"M/d/yyyy") doesn't work
in all cases. The other one does.
Click to expand...

Many users would be justifiably VERY ANGRY if your application messed
up their settings. It can be done, but a) it's not necessary and b)
generally a bad idea! How would you like it if you ran a program and
suddenly all your Word documents and Excel spreadsheets changed their
contents and appearance?

Format(item, "M/d/yyyy") should work in every case (for what you're
asking it to do - generate a String value like "7/15/2004"). If you
want to use dates input - in whatever format - as a criterion in a
query, you can set the Criterion to

Format([Enter date:], "mm\/dd\/yyyy")

and it should convert the date using the user's preferred settings
into a form which will search the database correctly.


.
Click to expand...
Click to expand...
 
I need this because a user has their format set up
as "M/d/yyyy" and it seems to make access not work
properly (see other message posted earlier under queries).
Click to expand...

I'm not reading the queries group: but once you understand how Access and
Jet use dates it is possible to create code that is bulletproof against all
international settings. It comes in three parts:

Jet (the db engine itself, reads SQL) _only_ interprets dates in a few
formats: #mm/dd/yyyy# and #yyyy-mm-dd# are the two most used. [1] Any date
in any SQL command must be in one of these forms, and this is regardless of
the computer's international settings, control panel, etc.

VBA will convert between dates (remember they are just double precision
numbers, under the skin) and text using (first) the control panel settings,
then any other kind of format until it succeeds. For example,

dtMyDate = #09/12/2004#

is in September on a US computer and December everywhere else. This _does_
depend on the control panel settings.

Access tries to hide one lot from the other, and often confuses the user in
the process. If you type 09/12/2004 as a criterion in the query grid,
outside US settings it will get changed round in the actual SQL in the
querydef; although you probably won't get to see it, because it will change
it back again when you look at it.

The bottom lines are:

If you are writing SQL yourself _ALWAYS_ use a Format() function to isolate
the code from the control panel:-
"... WHERE EndDate = " & Format(dtED, "\#yyyy\-mm\-dd\#") & " AND ..."

When using the GUI, just stick to the set up as fixed in the control panel
and don't try to double-guess the system: Access will practically always do
what you want right.

Hope that helps



Tim F



[1] Yes, there is a bug so that an invalid date like #23/04/2004# is
accepted by Jet as a valid date in April. Bah!
 
Back
Top