none
Ignore Regional settings when entering a date RRS feed

  • Question

  • I would like to be able to enter a date in a Date/Time field of an Access 2013 db, irrespective of the Regional settings.

    As an example, on a pc with say Regional Settings = English UK, I would like to store say 3rd October 2015 (3/10/2015) as 10/3/2015. In other words, if I now change Regional Settings = English USA, I would see 10/3/2015.

    How do I make sure that the double precision floating number where the date is stored stores the date which I want and not necessarily the date in the Regional Settings?

     
    Thursday, October 15, 2015 2:16 PM

Answers

  • You're mixing format and storage.

    Date/Time values are stored in a numeric fashion. What you see depends only on the output controls (whether or not they use the computers/clients locales).

    The stored Date/Time value has no information in which format it was entered.

    The only things which controls this are - as already said - the control format settings. Here you can specify an input and an output format in the table definition. Then these settings are used instead of the defaults.


    Thursday, October 15, 2015 2:39 PM
  • You have to enter a date according to the regional settings of the user. The date is stored as a number that does not depend on the regional settings. So when someone with USA settings enters 10/3/2015 and someone with UK settings enters 3/10/2015, the same value, namely 42280, will be stored.

    To avoid ambiguity, you could enter dates as 3-Oct-2015. If you set the Format property of the field / text box to Medium Date or the equivalent dd-mmm-yyyy, the dates will be displayed that way too.

    Or use the ISO format yyyy-mm-dd. The above date would be displayed as 2015-10-03. Windows allows entering dates this way, regardless of the user's regional settings.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 15, 2015 2:52 PM
  • For a brand new app what you mention is a good idea. As I mentioned, the calendar control cannot return an invalid date so it saves the effort required for testing.

    Hi John,

    I understand. In my applications you can enter a date just by typing in a control, or with a doubleclick on the control, to open the (homemade) calendar.

    DblClicking on an empty control set the calendar to today, DblClick on a non-empty control sets the calendar to that date.

    What the users really appreciate is, that when clicking a filled date control with the ">"-key (or the lower case equivalent ".", the date increase by one day. The same holds for "<" and "," to decrease the date by one day. An additional Shift key speeds it up to weeks.

    Imb.

    Friday, October 16, 2015 5:15 PM

All replies

  • You're mixing format and storage.

    Date/Time values are stored in a numeric fashion. What you see depends only on the output controls (whether or not they use the computers/clients locales).

    The stored Date/Time value has no information in which format it was entered.

    The only things which controls this are - as already said - the control format settings. Here you can specify an input and an output format in the table definition. Then these settings are used instead of the defaults.


    Thursday, October 15, 2015 2:39 PM
  • You have to enter a date according to the regional settings of the user. The date is stored as a number that does not depend on the regional settings. So when someone with USA settings enters 10/3/2015 and someone with UK settings enters 3/10/2015, the same value, namely 42280, will be stored.

    To avoid ambiguity, you could enter dates as 3-Oct-2015. If you set the Format property of the field / text box to Medium Date or the equivalent dd-mmm-yyyy, the dates will be displayed that way too.

    Or use the ISO format yyyy-mm-dd. The above date would be displayed as 2015-10-03. Windows allows entering dates this way, regardless of the user's regional settings.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 15, 2015 2:52 PM
  • So if I have UK settings and I format a textbox holding a date to show US settings (10/15/2015) can I ensure that 15th October 2015 is saved?
    Thursday, October 15, 2015 3:00 PM
  • Date 10/15/2015 is a bad example since it will be figured out automatically. Can a date such as 10/3/2015 (= 3rd October 2015) be saved as 3rd October 2015 if settings are UK?
    Thursday, October 15, 2015 3:21 PM
  • I'll stick to the 3rd of October since 15 is not a valid month number.

    If you use UK settings, enter 3/10/2015 and tab out of the text box, Access will store the number 42280 which corresponds to the 3rd of October 2015.

    If the Format property of the field/text box is set to US date format m/d/yyyy, the date you entered will be displayed as 10/3/2015. But the moment you click in the text box, you'll see 3/10/2015, since that is the way the 3rd of October is represented in your local settings.

    If you send the database to someone with US settings, (s)he will see 10/3/2015 whether the text box has focus or not.

    If you send the database to someone in Hungary, where yyyy/mm/dd is the standard date format, (s)he will see 2015/10/03 when the text box has focus (because of the local setting), and 10/3/2015 when the text box does not have focus (because the Format property imposes US format).

    The underlying value remains 42280 in all these situations.

    If you use UK regional settings and enter 10/3/2015, it will be interpreted as the 10th of March 2015. It will be displayed as 10/3/2015 if the Format of the text box corresponds to your UK settings, but as 3/10/2015 if the Format corresponds to US settings.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, October 15, 2015 3:23 PM
  • Hans, I believe you have addressed all possibilities.

    Many thanks for your response.

    Friday, October 16, 2015 5:22 AM
  • Hans, in my application the user can choose between several flags (the UK flag and the US flag being two of them) to handle the wording that appears on the labels, buttons etc on each form.

    I have a case where the pc has UK settings and the user may choose the US flag and therefore would expect US date formats etc. It is fine for existing dates since I can set the format of the textbox, but when I enter a new date it appears from what you mention that I would need to save in the db a new date entered of 10/3/2015 (corresponding to 3rd October 2015) to it UK equivalent of 3/10/2015. So once the latter is stored correctly it would appear correctly in the US format. In other words the storage of the date takes into account the Regional settings of the PC. Right?


    Will test is out.
    • Edited by JohnPapa05 Friday, October 16, 2015 6:08 AM
    Friday, October 16, 2015 6:08 AM
  • You need to change the input format of your controls.. but I wrote that already.
    Friday, October 16, 2015 7:36 AM
  • Thanks Stefan.

    There are two variables

    1) The manual setting of the language (done by the user selecting the language in the application)

    2) The regional settings

    The first would be done by something like

    If pL = 1 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 2 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 3 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 4 Then
      funSetDateFormat = "mm-dd-yyyy"
    ElseIf pL = 5 Then
      funSetDateFormat = "dd-mm-yyyy"
    End If

    where pL1 could be English UK and pL4 English US and a control would be set by

    Me.myControl.Format = funSetDateFormat

    All my dates are set with a custom made calendar which is made up of textboxes and I handle all the functionality. This has the advantage of portability across all versions of Access.

    In my case if I have UK settings combined with user selection of US then I simply need to reverse the Month and day and return this value to the textbox holding the date.

    Does this make sense?

    Friday, October 16, 2015 7:57 AM
  • If pL = 1 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 2 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 3 Then
      funSetDateFormat = "dd-mm-yyyy"
    ElseIf pL = 4 Then
      funSetDateFormat = "mm-dd-yyyy"
    ElseIf pL = 5 Then
      funSetDateFormat = "dd-mm-yyyy"
    End If

    Hi John,

    A little off-topic: I like short programming. I typically would use:

    Select Case pL
    Case 4:     funSetDateFormat = "mm-dd-yyyy"
    Case Else:  funSetDateFormat = "dd-mm-yyyy"
    End Select
    

    Imb.

    Friday, October 16, 2015 8:33 AM
  • When the user enters or edits a date, Access/Windows will always interpret it according to the user's regional settings, regardless of the format set for the field or text box.

    So if the user has UK settings, (s)he will have to enter the 3rd of October as 3/10/2015, even if the format of the field/text box is set to US date format m/d/yyyy.

    To make Access interpret 10/3/2015 as the 3rd of October in UK regional settings would be complicated.

    To avoid all ambiguity, you could use separate text boxes for day, month and year, and assemble the date from those using the DateSerial function.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 16, 2015 10:07 AM
  • To make Access interpret 10/3/2015 as the 3rd of October in UK regional settings would be complicated.

    Thanks Hans,

    I do not seem to have a choice as many users will be using the same pc with the Settings to UK and some of the users will select the US flag.

    I have tried it and it seems ok. I have been very consistent in changing dates so I would need to change only the custom calendar. I hope.

    John

    Friday, October 16, 2015 12:22 PM
  • I do not seem to have a choice as many users will be using the same pc with the Settings to UK and some of the users will select the US flag.

    I have tried it and it seems ok. I have been very consistent in changing dates so I would need to change only the custom calendar. I hope.

    Hi John,

    Depending on the UK- or US-flag you could also exchange Day and Month in the BeforeUpdate event, to convert the one format to the other.

    I haven't it tried myself, but I think it can work.

    Imb.

    Addition: A kind of analogues situation I have for decimal numbers, where users can enter or an decimal comma or an decimal point at will.


    • Edited by Imb-hb Friday, October 16, 2015 1:41 PM addition
    Friday, October 16, 2015 1:36 PM
  • To make Access interpret 10/3/2015 as the 3rd of October in UK regional settings would be complicated.

    Hi John,

    I checked how I handle date controls.

    - I do not use any formatting in the control. I only define Textalign = 3 (rightaligned). I don't want to see the Access messages when the date is invalid.

    - In the BeforeUpdate event I do a check for a valid date. If invalid, you cannot leave the control, until the control is Null or has a valid date.

    - In the AfterUpdate event I format the date to a readable value with the month in characters:   03-okt-2015, but you can use any formatting. In this way the user can immediately see, without ambiguity, if the date is correct.

     

    Imb.

    Friday, October 16, 2015 2:14 PM
  • Thanks Imb-hd,

    Since all dates are handled by my custom made calendar popup, then this would check whether the user chosen language does not correspond to the pc setting and do what you mention,

    John

    Friday, October 16, 2015 3:18 PM
  • Hi Imb,

    For a brand new app what you mention is a good idea. As I mentioned, the calendar control cannot return an invalid date so it saves the effort required for testing.

    John

    Friday, October 16, 2015 3:20 PM
  • For a brand new app what you mention is a good idea. As I mentioned, the calendar control cannot return an invalid date so it saves the effort required for testing.

    Hi John,

    I understand. In my applications you can enter a date just by typing in a control, or with a doubleclick on the control, to open the (homemade) calendar.

    DblClicking on an empty control set the calendar to today, DblClick on a non-empty control sets the calendar to that date.

    What the users really appreciate is, that when clicking a filled date control with the ">"-key (or the lower case equivalent ".", the date increase by one day. The same holds for "<" and "," to decrease the date by one day. An additional Shift key speeds it up to weeks.

    Imb.

    Friday, October 16, 2015 5:15 PM