A problem I am finding with using UK date formats is with text boxes that are used for date entry. I am using Win NT 4 and the date formats in regional setting are correct for UK ie dd/mm/yyyy. When I put in an unambiguous date into the control eg 13/04/2001 this gets entered correctly into the table as 13/04/2001 but if I put a date such as 12/04/2001 this ends up as 04/12/2001 which is treating it as a US date, not what is wanted. It appears that the textbox does not take the regional setting properly in to account. Input masks are no use for this problem. Has anyone had this problem? Is this bug fixable?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Further UK Date Problems 1
- Thread starter PaulCoop
- Start date
-
1
- #2
petermeachem
Programmer
have you set the format for the text box? Peter Meachem
peter@accuflight.com
peter@accuflight.com
Peter has a good point--you need to be certain you understand whether the date is actually stored improperly, or only displayed improperly.
It may be that the Format property is set to something like "MM/dd/yyyy". If the database was created and tested in the US, no problem would likely be noted until you use it in the UK. Specifying a named format, such as "General Date", will usually allow the format to adapt to the user's Control Panel Regional Settings. (The exception is Medium Date, which depends on the language edition of Access.)
If the date is actually stored improperly, it may be because code in the form control's AfterUpdate event procedure is manipulating the date components, and is not designed to adapt to the regional settings. This is quite common with dates being entered into unbound text boxes, because in that case Access doesn't automatically convert the text to a date value. The code can be corrected to resolve the problem. Rick Sprague
It may be that the Format property is set to something like "MM/dd/yyyy". If the database was created and tested in the US, no problem would likely be noted until you use it in the UK. Specifying a named format, such as "General Date", will usually allow the format to adapt to the user's Control Panel Regional Settings. (The exception is Medium Date, which depends on the language edition of Access.)
If the date is actually stored improperly, it may be because code in the form control's AfterUpdate event procedure is manipulating the date components, and is not designed to adapt to the regional settings. This is quite common with dates being entered into unbound text boxes, because in that case Access doesn't automatically convert the text to a date value. The code can be corrected to resolve the problem. Rick Sprague
- Thread starter
- #4
Yes the format of the text box is correctly set to short date and the layout of this is taken from the regional settings.
I did find the solution to the problem and this was in the fact that I was using Docmd.RunSQL "INSERT INTO ..." to enter the data into the table. In SQL statements dates have to be in US format (I was putting it in UK format) hence the reversing in the table. The interesting knock on effect is that if you put in unambiguous date (eg 14/03/2001) in UK format into a SQL statement Access converts it to US format rather than reject it.
Paul Cooper
I did find the solution to the problem and this was in the fact that I was using Docmd.RunSQL "INSERT INTO ..." to enter the data into the table. In SQL statements dates have to be in US format (I was putting it in UK format) hence the reversing in the table. The interesting knock on effect is that if you put in unambiguous date (eg 14/03/2001) in UK format into a SQL statement Access converts it to US format rather than reject it.
Paul Cooper
petermeachem
Programmer
Always wise to format dates so put
format(Mydate,"dd/mmm/yyyy"
That makes sure it will work ok. Peter Meachem
peter@accuflight.com
format(Mydate,"dd/mmm/yyyy"
That makes sure it will work ok. Peter Meachem
peter@accuflight.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question