A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

displaying dates in UK format



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2009, 03:01 AM posted to microsoft.public.access.forms
Mortimer14
external usenet poster
 
Posts: 7
Default displaying dates in UK format

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!


  #2  
Old December 22nd, 2009, 06:12 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default displaying dates in UK format

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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!


  #3  
Old December 23rd, 2009, 01:25 AM posted to microsoft.public.access.forms
Mortimer14
external usenet poster
 
Posts: 7
Default displaying dates in UK format

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



"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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!


.

  #4  
Old December 23rd, 2009, 02:17 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default displaying dates in UK format

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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



"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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!


.

  #5  
Old December 23rd, 2009, 04:17 AM posted to microsoft.public.access.forms
Mortimer14
external usenet poster
 
Posts: 7
Default displaying dates in UK format

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



"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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



"Allen Browne" wrote:

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?


  #6  
Old December 23rd, 2009, 08:14 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default displaying dates in UK format

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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



"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" wrote in message
...
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



"Allen Browne" wrote:

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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.