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? |
Thread Tools | |
Display Modes | |
|
|