Date Conversion issue

J

jason.evans

Hi

I am having an issue with Date formats. I am working on a network where
the regional setting locale defaults to US and is locked down so there
is no access to change this. Anyhow there are many hundreds of users
and it would be extremely inefficient to try to communicate to everyone
that they need to change their regional settings and how.

I have a textbox into which the date needs to be entered. It is
formatted as dd-mmm-yy.

When the user enters a date it displays in US Format. ie if 04-06-98 is
entered, it will display 06-Apr-98. I want it to display 04-Jun-98

How do I get my textbox to display the dat as enetered by the user.

Many thanks
 
J

John Vinson

Hi

I am having an issue with Date formats. I am working on a network where
the regional setting locale defaults to US and is locked down so there
is no access to change this. Anyhow there are many hundreds of users
and it would be extremely inefficient to try to communicate to everyone
that they need to change their regional settings and how.

I have a textbox into which the date needs to be entered. It is
formatted as dd-mmm-yy.

When the user enters a date it displays in US Format. ie if 04-06-98 is
entered, it will display 06-Apr-98. I want it to display 04-Jun-98

How do I get my textbox to display the dat as enetered by the user.

Many thanks
Click to expand...

There's no need to *convert* the date. A date is not stored as a
formatted text string; it's stored as a number, a count of days (and
fractions of a day for times) from a start point.

Simply open the Form in design view and set the Format property of
this textbox to dd-mmm-yy (or, if there's the slightest chance of
dates prior to January 1, 1930, to dd-mmm-yyyy). Also set the Input
Mask property (if you insist on users entering dates in this format)
to 00-aaa-00 (or -0000 as appropriate).

John W. Vinson[MVP]
 
J

jason.evans

Thanks John. Thats a good suggestion.

I do have the format property set that way for display purposes. I dont
want to limit the user to entering the date specifically that way
though, hence no input mask. This is to be flexible and user friendly
on the data entry aspect. The problem with that as I said earlier is
that 04-06-98 is interpreted and displayed by the system as 06-Apr-98.

Is there no other way around this?

If not then obviously this will be the best solution

Thanks again
 
D

Douglas J Steele

You stated that the regional setting has been locked down to US format
(mm/dd/yyyy). Because of that, no, there's no real way to have Access
interpret 04-06-98 as anything other than 06-Apr-98 (not if you want to use
a bound text box).

You say that you don't want to limit how the user enters the date: how would
you know whether they meant 04-Jun-98 or 06-Apr-98 if you're not limiting
them?

If you've got legitimate reasons to be using dd-mm-yyyy format, you should
take it up with your IS department so that they unlock the ability to change
regional settings.
 
J

jason.evans

Thanks Doug.

I am in fact using an unbound text box if that makes any difference?

The only users are in Australia so will be entering in the order day
month year. I though from a data entry perspective that it would be
easier for users not to have to comply with entering a specific format
via a mask but appears to be unavoidable.

As for the IS dept., well that would make sense theoretically.
Unfortunately not as simple as you would think it would be.

One final question (and apologies if this is asking the totally
obvious) - What if my app needed to be used in geographically different
locations and hence the regional settings differed across locations? Is
masking the text box the only way to make this usable in both
scenarios?

Many thanks
 
D

Douglas J Steele

If it's an unbound text box, you could break the input into its component
parts (using the Split function, perhaps) and then recombine it to the
actual date using the DateSerial function.


Masks don't really provide anything (although forcing an alpha date
obviously will work)

Unfortunately, the answer to your last question is "is depends". In many
places, Access respects the user's Short Date format set through Regional
Settings. It definitely doesn't in SQL statements, though.

You might want to read what Allen Browne has at
http://www.allenbrowne.com/ser-36.html or what I wrote in my September, 2003
Access Answers column in Pinnacle Publication's Smart Access (you can
download the column and the accompanying database for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)
 
Top