Simple but profound

[kennykee]

Hi, I have a question which cracks my head.

Assume I have a table with a field with the name "Date" and in fact it is a
date field.

Usually, user will fill the date fill with {[month][day][year]}. However, I
want to make it as {[day][month][year]}.

Usually to western date style, if I enter 8 2 06 to a field, it will changes
to 2-Aug-06 for medium date.

Example that I needed:

input to field: 8 2 06
automatic changes to field: 8-Feb-06 instead of 2-Aug-2006

Please enlighten me.

Thanks in advance

kenny

 

[Wayne-I-M]

Hi

Change the format of the field to

dd-mmm-yy

Oh and change the field name from date to something else

 

[Rick Brandt]

Hi, I have a question which cracks my head.

Assume I have a table with a field with the name "Date" and in fact
it is a date field.

Usually, user will fill the date fill with {[month][day][year]}.
However, I want to make it as {[day][month][year]}.

Usually to western date style, if I enter 8 2 06 to a field, it will
changes to 2-Aug-06 for medium date.

Example that I needed:

input to field: 8 2 06
automatic changes to field: 8-Feb-06 instead of 2-Aug-2006

Please enlighten me.

Thanks in advance

kenny

Dates are always stored exactly the same. The format property controls what you
see and that format is applied after you leave the field regardless of the
format the user chooses to key the date in.

If the user enters an ambiguous format Access will assume it is being entered in
the format in your Windows Regional settings unless what is entered would be an
invalid date in that format. In that case it will try other formats in a "best
guess" attempt at what the user intends.

If you want to force a particular format for *entry* then you use an InputMask
with the desired pattern. Otherwise the user can type the date as they wish and
can verify that what they entered was interpretted correctly when they tab out
(if you use a non-ambiguous display format). The latter is what most people
recommend.

NOTE: Date literals typed into code and queries with an ambiguous format will
always be interpretted as US format m/d/yy or m/d/yyyy unless that would result
in an invalid date. In these areas the Windows regional settings do not matter.
Because of that consistently using a non-ambiguous format like ISO yyyy-mm-dd is
recommended. This is particularly an issue when your app prompts the user for a
date literal that will be used in a code routine or query. They cannot assume
that their regioanl preferences will be honored.

 

[kennykee]

Hi,

Thanks for you help but I still need something more.

How can I change the US date interpretation format into ddmmmyy instead of
mmmddyy in a table? It is almost same like adding a css into html code.

Thanks in advance

kenny

Hi, I have a question which cracks my head.

Assume I have a table with a field with the name "Date" and in fact
it is a date field.

Usually, user will fill the date fill with {[month][day][year]}.
However, I want to make it as {[day][month][year]}.

Usually to western date style, if I enter 8 2 06 to a field, it will
changes to 2-Aug-06 for medium date.

Example that I needed:

input to field: 8 2 06
automatic changes to field: 8-Feb-06 instead of 2-Aug-2006

Please enlighten me.

Thanks in advance

kenny

Dates are always stored exactly the same. The format property controls what you
see and that format is applied after you leave the field regardless of the
format the user chooses to key the date in.

If the user enters an ambiguous format Access will assume it is being entered in
the format in your Windows Regional settings unless what is entered would be an
invalid date in that format. In that case it will try other formats in a "best
guess" attempt at what the user intends.

If you want to force a particular format for *entry* then you use an InputMask
with the desired pattern. Otherwise the user can type the date as they wish and
can verify that what they entered was interpretted correctly when they tab out
(if you use a non-ambiguous display format). The latter is what most people
recommend.

NOTE: Date literals typed into code and queries with an ambiguous format will
always be interpretted as US format m/d/yy or m/d/yyyy unless that would result
in an invalid date. In these areas the Windows regional settings do not matter.
Because of that consistently using a non-ambiguous format like ISO yyyy-mm-dd is
recommended. This is particularly an issue when your app prompts the user for a
date literal that will be used in a code routine or query. They cannot assume
that their regioanl preferences will be honored.

 

[Rick Brandt]

Hi,

Thanks for you help but I still need something more.

How can I change the US date interpretation format into ddmmmyy
instead of mmmddyy in a table? It is almost same like adding a css
into html code.

If you open the table in design view and set the format property of that field
to "ddmmmyy" then that is how the dates will be *displayed* when you look at the
table. That same format property will need to be used on every form and report
though because formatting is not inherited.

Just to restate though...this will have no effect on the value that is actually
stored. Only what it looks like.

 

[kennykee]

Hi,

Thanks again for your help.
Finally, I tried an alternative idea that is by changing the windows
regional setting and it works.

 

[Douglas J. Steele]

Check Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[fredg]

Hi, I have a question which cracks my head.

Assume I have a table with a field with the name "Date" and in fact it is a
date field.

Usually, user will fill the date fill with {[month][day][year]}. However, I
want to make it as {[day][month][year]}.

Usually to western date style, if I enter 8 2 06 to a field, it will changes
to 2-Aug-06 for medium date.

Example that I needed:

input to field: 8 2 06
automatic changes to field: 8-Feb-06 instead of 2-Aug-2006

Please enlighten me.

Thanks in advance

kenny

In addition to what every one else has suggested,
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'