displaying dates in UK format

[Mortimer14]

I didn't write the original database and my knowledge of database design is
not good enough to duplicate the work already done.

With a variety of computers running Windows98 and/or XP, using Access2003, I
cannot get dates to display on my forms in UK format (i.e. dd/mm/yyyy or even
d/m/yy).

The computers in use all have the short date format set to dd/mm/yyyy in
regional settings.

I have read through http://allenbrowne.com/ser-36.html and tried the
formatting suggested there and it didn't work for me. I added it to "Lost
focus" and "OnExit" and even "AfterUpdate" and still the date switches back
to american format (mm/dd/yyyy) when it is displayed.

The Format property has been set to "LongDate", "MediumDate", "ShortDate"
and even to "dd/mm/yyyy" with no luck. Similarly, the format on date fields
in the table have also been set to the above.

If I enter a date such as 5/12/2009, the display switches to:
12/5/2009
12/May/2009
or the long form of that depending on what format the form control was set to.

The only dates that don't get switched are those where there is no doubt
which is the day and which is the month, such as 18/12/2009.

I have tried everything that I can think of, including switching my fields
to unbound and assigning the input to the appropriate table fields in the
desired format.

Nothing works.

I need to see the dates in UK format, nothing else is acceptable. Please help!

 

[Allen Browne]

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

For (a), IME, its best to leave the Format and Input Mask properties blank
everywhere - in the table, in the query, and in the form. Access will then
respect your local regional settings.

For (c) and (d), it's best to set the Format property of the text box on the
form to General Date. This won't suppress any date or time components, but
it will help Access understand the data type.

If you use parameters in a query (including things like
[Forms].[Form1].[txtStartDate]), be sure to declare them so JET knows the
data type.

That should work for all versions of Access, and all versions of Windows.

 

[Mortimer14]

Thank you for your reply.

This would be "a" - fields that come from native Access tables. No queries,
no calculated fields, just an input form that takes several dates and stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried setting
the field to "General Date", and still the dates switch to american format.

I can sometimes get the dates to display correctly, but then I close the
form (or the db) and re-open it and everything goes back to american format.

The only thing that seems to work is to enter the dates in american format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the month,
that way access leaves the date in the desired format.
---
Mortimer14

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

For (a), IME, its best to leave the Format and Input Mask properties blank
everywhere - in the table, in the query, and in the form. Access will then
respect your local regional settings.

For (c) and (d), it's best to set the Format property of the text box on the
form to General Date. This won't suppress any date or time components, but
it will help Access understand the data type.

If you use parameters in a query (including things like
[Forms].[Form1].[txtStartDate]), be sure to declare them so JET knows the
data type.

That should work for all versions of Access, and all versions of Windows.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I didn't write the original database and my knowledge of database design
is
not good enough to duplicate the work already done.

With a variety of computers running Windows98 and/or XP, using Access2003,
I
cannot get dates to display on my forms in UK format (i.e. dd/mm/yyyy or
even
d/m/yy).

The computers in use all have the short date format set to dd/mm/yyyy in
regional settings.

I have read through http://allenbrowne.com/ser-36.html and tried the
formatting suggested there and it didn't work for me. I added it to "Lost
focus" and "OnExit" and even "AfterUpdate" and still the date switches
back
to american format (mm/dd/yyyy) when it is displayed.

The Format property has been set to "LongDate", "MediumDate", "ShortDate"
and even to "dd/mm/yyyy" with no luck. Similarly, the format on date
fields
in the table have also been set to the above.

If I enter a date such as 5/12/2009, the display switches to:
12/5/2009
12/May/2009
or the long form of that depending on what format the form control was set
to.

The only dates that don't get switched are those where there is no doubt
which is the day and which is the month, such as 18/12/2009.

I have tried everything that I can think of, including switching my fields
to unbound and assigning the input to the appropriate table fields in the
desired format.

Nothing works.

I need to see the dates in UK format, nothing else is acceptable. Please
help!

.

 

[Allen Browne]

From the behaviour you describe, I'm not convinced you have UK date formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

For (a), IME, its best to leave the Format and Input Mask properties
blank
everywhere - in the table, in the query, and in the form. Access will
then
respect your local regional settings.

For (c) and (d), it's best to set the Format property of the text box on
the
form to General Date. This won't suppress any date or time components,
but
it will help Access understand the data type.

If you use parameters in a query (including things like
[Forms].[Form1].[txtStartDate]), be sure to declare them so JET knows the
data type.

That should work for all versions of Access, and all versions of Windows.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I didn't write the original database and my knowledge of database
design
is
not good enough to duplicate the work already done.

With a variety of computers running Windows98 and/or XP, using
Access2003,
I
cannot get dates to display on my forms in UK format (i.e. dd/mm/yyyy
or
even
d/m/yy).

The computers in use all have the short date format set to dd/mm/yyyy
in
regional settings.

I have read through http://allenbrowne.com/ser-36.html and tried the
formatting suggested there and it didn't work for me. I added it to
"Lost
focus" and "OnExit" and even "AfterUpdate" and still the date switches
back
to american format (mm/dd/yyyy) when it is displayed.

The Format property has been set to "LongDate", "MediumDate",
"ShortDate"
and even to "dd/mm/yyyy" with no luck. Similarly, the format on date
fields
in the table have also been set to the above.

If I enter a date such as 5/12/2009, the display switches to:
12/5/2009
12/May/2009
or the long form of that depending on what format the form control was
set
to.

The only dates that don't get switched are those where there is no
doubt
which is the day and which is the month, such as 18/12/2009.

I have tried everything that I can think of, including switching my
fields
to unbound and assigning the input to the appropriate table fields in
the
desired format.

Nothing works.

I need to see the dates in UK format, nothing else is acceptable.
Please
help!

.

 

[Mortimer14]

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of "English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14

From the behaviour you describe, I'm not convinced you have UK date formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

 

[Allen Browne]

Well, I don't have the same problem you describe, and here's how my regional
settings look:
http://temp.allenbrowne.com/DateFormat.jpg

I don't suppose we are talking about Access 2? It handled dates differently.

We've already confirmed that neither the Format nor Input Mask are
interfering, so it must be something else. VBA code or macros?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of "English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14

From the behaviour you describe, I'm not convinced you have UK date
formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close
the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american
format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14



:

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

 

[Mortimer14]

Thank you for your time.

That's not a Win98 screen though (vista maybe?).

It almost seems like there is a setting that is wrong somewhere as I can get
the display to work until I close and open the form, and some other PC's
display the dates correctly with the same database (not just a copy).

I will keep working on it over the christmas break.

Happy Holidays.

-------------

Well, I don't have the same problem you describe, and here's how my regional
settings look:
http://temp.allenbrowne.com/DateFormat.jpg

I don't suppose we are talking about Access 2? It handled dates differently.

We've already confirmed that neither the Format nor Input Mask are
interfering, so it must be something else. VBA code or macros?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of "English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14

From the behaviour you describe, I'm not convinced you have UK date
formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close
the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american
format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14



:

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

.

 

[Mortimer14]

Sorry, I hadn't read through your entire response before posting....

Help \ About shows:
Microsoft Office Access 2003 (11.8166.8221) SP3
Part of Microsoft Office Professional Edition 2003
Copyright 1992-2003 Microsoft Corporation. All rights reserved.

I have copied the database locally for testing purposes. I'm running on
WinXP SP3. The user who reported the problem is on Win98 (not sure which
service pack), and we are experiencing the same problem. The database is
normally stored on a windows 2000 fileserver and accessed through a network
connection.

--
Mortimer14

Well, I don't have the same problem you describe, and here's how my regional
settings look:
http://temp.allenbrowne.com/DateFormat.jpg

I don't suppose we are talking about Access 2? It handled dates differently.

We've already confirmed that neither the Format nor Input Mask are
interfering, so it must be something else. VBA code or macros?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of "English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14

From the behaviour you describe, I'm not convinced you have UK date
formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close
the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american
format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14



:

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

.

 

[Mortimer14]

I have confirmed that the input mask and format are blank.

I also confirmed that this isn't just a display problem, the dates are being
stored in their altered form. E.G. Enter 7/12/2009 and the display switches
it to 12/7/2009 and stores it in the table as 40006 which translates to 12
July 2009. Similarly enter 6/12/2009 watch the display switch it to 12/6/2009
and store it as 39976 (12 June 2009).

--
Mortimer14

Well, I don't have the same problem you describe, and here's how my regional
settings look:
http://temp.allenbrowne.com/DateFormat.jpg

I don't suppose we are talking about Access 2? It handled dates differently.

We've already confirmed that neither the Format nor Input Mask are
interfering, so it must be something else. VBA code or macros?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of "English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14

From the behaviour you describe, I'm not convinced you have UK date
formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close
the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american
format
so that when access swaps the day and month, the resulting date will be
correct. The alternative is to not use any of the first 12 days of the
month,
that way access leaves the date in the desired format.
---
Mortimer14



:

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

.

 

[Allen Browne]

I've used Access 98 (with Access 97 and earlier), and didn't have the
problem you describe, so I don't know what's going on. (The screenshot was
for Win7.)

Access will spin the dates around at entry time if it can't make sense of
them, so if the problem is occurring it makes sense that it would store them
that way.

I've no idea why your case is doing the weird thing. It's not like this
machine is actually running Access in a Terminal Server session or
something?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have confirmed that the input mask and format are blank.

I also confirmed that this isn't just a display problem, the dates are
being
stored in their altered form. E.G. Enter 7/12/2009 and the display
switches
it to 12/7/2009 and stores it in the table as 40006 which translates to 12
July 2009. Similarly enter 6/12/2009 watch the display switch it to
12/6/2009
and store it as 39976 (12 June 2009).

--
Mortimer14

Well, I don't have the same problem you describe, and here's how my
regional
settings look:
http://temp.allenbrowne.com/DateFormat.jpg

I don't suppose we are talking about Access 2? It handled dates
differently.

We've already confirmed that neither the Format nor Input Mask are
interfering, so it must be something else. VBA code or macros?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you again for your quick reply.

Start \ Control Panel \ Regional Settings \ "Customize" button \ Date
Tab

Under Short Date format I have dd / MM / yyyy
Date Separator: /

Short Date Sample 23/12/2009

Now the default language had been set to "English US" instead of
"English
Australia". I don't see how that could have had anything to do with my
problem.

--
Mortimer14



:

From the behaviour you describe, I'm not convinced you have UK date
formats
set in the Regional Settings of the Windows Control Panel.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thank you for your reply.

This would be "a" - fields that come from native Access tables. No
queries,
no calculated fields, just an input form that takes several dates
and
stores
them in a table (along with other information of course).

I have tried leaving the format and input mask blank, and I've tried
setting
the field to "General Date", and still the dates switch to american
format.

I can sometimes get the dates to display correctly, but then I close
the
form (or the db) and re-open it and everything goes back to american
format.

The only thing that seems to work is to enter the dates in american
format
so that when access swaps the day and month, the resulting date will
be
correct. The alternative is to not use any of the first 12 days of
the
month,
that way access leaves the date in the desired format.
---
Mortimer14



:

Are we talking about:
a) fields that come from native Access tables?
b) attached tables with fields that come from a non-JET database?
c) calculated fields, e.g. Nz([SomeField], Date())
d) unbound controls on a form?

.

 

[Mortimer14]

No, it's not --- "It's not like this machine is actually running Access in a
Terminal Server session ".

I am connected to a network, but the database is stored on my local
harddrive, not on a network drive. I can disconnect from the network and
still experience the same problems.

Oddly enough, I copied the DB and loaded it on a Vista PC using Office2007
and the dates stayed the way they were entered. This leads me to believe that
there is some setting somewhere in access or in Win98 / WinXP that is causing
this problem.

The only thing left would be to set the date entry fields to current date
using =FORMAT(DATE(),"DD/MM/YYYY") and not allow changes. This, at least,
seems to work and most of the time the current date is correct anyway.

Thank you for your time and consideration.