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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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? |
#7
|
|||
|
|||
displaying dates in UK format
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" 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? . |
#8
|
|||
|
|||
displaying dates in UK format
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" 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? . |
#9
|
|||
|
|||
displaying dates in UK format
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" 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? . |
#10
|
|||
|
|||
displaying dates in UK format
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" wrote in message ... 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" 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 | |
|
|