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!


0
Utf
12/22/2009 3:01:01 AM
access.forms 6864 articles. 2 followers. Follow

10 Replies
839 Views

Similar Articles

[PageSpeed] 55

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" <Mortimer14@discussions.microsoft.com> wrote in message 
news:2628316E-2B7D-4D0E-BB08-FC2F0A1C83DB@microsoft.com...
> 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!
>
> 
0
Allen
12/22/2009 6:12:58 AM
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" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:2628316E-2B7D-4D0E-BB08-FC2F0A1C83DB@microsoft.com...
> > 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!
> >
> > 
> .
> 
0
Utf
12/23/2009 1:25:01 AM
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" <Mortimer14@discussions.microsoft.com> wrote in message 
news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
> 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" <Mortimer14@discussions.microsoft.com> wrote in message
>> news:2628316E-2B7D-4D0E-BB08-FC2F0A1C83DB@microsoft.com...
>> > 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!
>> >
>> >
>> .
>> 
0
Allen
12/23/2009 2:17:53 AM
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" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
> > 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?
> >>

0
Utf
12/23/2009 4:17:02 AM
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" <Mortimer14@discussions.microsoft.com> wrote in message 
news:F6C0031B-C4C8-453A-8097-DE7A11021857@microsoft.com...
> 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" <Mortimer14@discussions.microsoft.com> wrote in message
>> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
>> > 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?
>> >>
> 
0
Allen
12/23/2009 8:14:54 AM
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.

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

"Allen Browne" wrote:

> 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" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:F6C0031B-C4C8-453A-8097-DE7A11021857@microsoft.com...
> > 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" <Mortimer14@discussions.microsoft.com> wrote in message
> >> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
> >> > 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?
> >> >>
> > 
> .
> 
0
Utf
12/23/2009 11:22:01 PM
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


"Allen Browne" wrote:

> 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" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:F6C0031B-C4C8-453A-8097-DE7A11021857@microsoft.com...
> > 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" <Mortimer14@discussions.microsoft.com> wrote in message
> >> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
> >> > 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?
> >> >>
> > 
> .
> 
0
Utf
12/23/2009 11:35:01 PM
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


"Allen Browne" wrote:

> 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" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:F6C0031B-C4C8-453A-8097-DE7A11021857@microsoft.com...
> > 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" <Mortimer14@discussions.microsoft.com> wrote in message
> >> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
> >> > 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?
> >> >>
> > 
> .
> 
0
Utf
12/23/2009 11:56:01 PM
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Mortimer14" <Mortimer14@discussions.microsoft.com> wrote in message 
news:3303A6A1-5389-4E81-92D0-DF81E0E5E5C7@microsoft.com...
> 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
>
>
> "Allen Browne" wrote:
>
>> 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" <Mortimer14@discussions.microsoft.com> wrote in message
>> news:F6C0031B-C4C8-453A-8097-DE7A11021857@microsoft.com...
>> > 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" <Mortimer14@discussions.microsoft.com> wrote in message
>> >> news:24CBF20C-4411-469F-B25F-DA54A854E039@microsoft.com...
>> >> > 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?
>> >> >>
>> >
>> .
>> 
0
Allen
12/24/2009 3:11:07 AM
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. 
--
Mortimer14

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
> "Mortimer14" <Mortimer14@discussions.microsoft.com> wrote in message 
> news:3303A6A1-5389-4E81-92D0-DF81E0E5E5C7@microsoft.com...
> > 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
> >
> >

0
Utf
1/4/2010 11:57:01 PM
Reply:

Similar Artilces: