locked
Date format changes in Access 2013 forms RRS feed

  • Question

  • I have an Access 2013 form with a field in medium date format (eg. 29-Nov-16), but when I click on the field, the value re-displays in short date format (eg. 2016-11-29) (which ironically is longer and doesn't fit).  I've searched online and can't find any explanation.  How can I stop this behavior?
    Tuesday, November 29, 2016 5:28 PM

All replies

  • Hi,

    Just a guess but check if the Format property in the table and the form are not the same. What do you have in each?

    Tuesday, November 29, 2016 5:33 PM
  • Thanks for the reply, DBguy.  That was my first thought too, but the format property in the form and its underlying query and the table under that are all medium date.

    Two other things that may help is that I developed this in Access 2007 and then upgraded to Access 2013; also, the form is a subform within a larger form, though I don't think that should matter.
    Tuesday, November 29, 2016 7:36 PM
  • Hi ,

    Try to change the date/time format from the setting of windows and make the long format like the same with the short format 

    Tuesday, November 29, 2016 7:41 PM
  • Hi MOKDEV,

    So you're guessing that it uses the Windows setting to edit the value?  2016-11-29 is indeed how Windows is displaying my date on the task bar, so that would make sense.  But I'm hesitant to change a setting that affects all of the applications in all of Windows.  What if I wanted one format in one app and another format in another?  Or two different formats in different forms of my Access database?  What I really want to do is tell Access to use the format I told it to, instead of the Windows setting.

    Tuesday, November 29, 2016 7:49 PM
  • Hi,

    Glad to hear we're getting somewhere. The thing to remember about the Format property is it is used for "display" purposes only. What the data (date) looks like internally does not change.

    Now, as you (or MOKDEV) said, it's possible Access uses the Windows Settings when you actually enter and change the data but then displays it afterwards using the Format you assigned within Access.

    You don't have any code running in the GotFocus event of the Date control, do you?

    Just curious...

    Tuesday, November 29, 2016 8:05 PM
  • Hi DBguy,

    No, no code on this field. Also, I looked for a property that seemed like it was the format for "editing" purposes, but I couldn't see one in the form.  Is there one?

    Tuesday, November 29, 2016 8:18 PM
  • You might have to put an input mask on that control. I'm not seeing that behavior in 2010, but things might have changed in 2013.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, November 29, 2016 8:37 PM
  • Bill might be on to something but just remember if you're using the built-in date picker, it will stop working once you use an Input Mask (at least it does in 2010).

    Also, I am not aware of any property other than Format to dictate how the date is displayed while editing.

    Good luck!

    Tuesday, November 29, 2016 9:00 PM
  • Hi Bill,

    Thanks for the suggestion.  I had tried that too, but it didn't help.  The input mask affects only the inputting of a new value (by adding ugly, hard to work with placeholder characters), but not the editing of an existing value (that shows up in short date format, presumably according to the Windows setting). 

    Bizarrely, it also affects the display, even after you delete the mask!
    Tuesday, November 29, 2016 9:33 PM
  • Hi DBGuy,

    I think I will be avoiding input masks given the way they're implemented.

    Good to have some reassurance I'm not missing an obvious property.

    No luck yet.

    Tuesday, November 29, 2016 9:35 PM
  • Hi DBGuy,

    I think I will be avoiding input masks given the way they're implemented.

    Good to have some reassurance I'm not missing an obvious property.

    No luck yet.

    Just to verify MOKDEV's suggestion, you could temporarily adjust your Regional Settings to see if the problem goes away. At least we can confirm if it's a viable solution. Just a thought...
    Tuesday, November 29, 2016 9:53 PM
  • I changed the Windows short date format to dd-MMM-yy, and that did change the format of the date for editing purposes, so at least we know that's where Access is getting it.

    I still remain in the quest, as I don't consider that a viable solution for the reasons above...

    Tuesday, November 29, 2016 11:23 PM
  • Hi,

    Thanks for checking. Let us know if you find out anything. If I run into something, I'll let you know. Cheers!

    Wednesday, November 30, 2016 12:01 AM
  • Hi hitrumpsai,

    in your last post you had mentioned that,"I changed the Windows short date format to dd-MMM-yy, and that did change the format of the date for editing purposes, so at least we know that's where Access is getting it."

    so from that we can understand that now you know the root cause of the issue.

    other community member already suggested you a different suggestions for date format. which you think that can't work for you.

    currently we have only these ways to change the format.

    if you want any further suggestion on this issue then let us know about that.

    you had mentioned that it is a field on a form.

    so is it Textbox? if yes then you can try to code on it's event. and set the format again when you click on the textbox. this is just a work around that you can try.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 30, 2016 3:04 AM
  • Hi Deepak,

    I do still need further suggestions for controlling the editing format of the date, because the one suggestion (input mask) didn't work (didn't affecting the editing format), and the other suggestion is not practical (because in addition to changing the editing format for one field it also changes the date format system-wide in Windows, in all date fields, all forms, and all applications, which I should not have to do to change the date format in one place on my form, or even application-wide in one application).

    The root cause of the issue is the apparent lack of control over the date format when editing one field or using one app.  When you say "currently we have only these ways to change the format", do you mean that Access does not give me this control on a field by field, or even application by application basis?

    If so, then I would put in a request that this feature be added to Access 2013, because it is very confusing to the user to have the dates suddenly change format every time he clicks on them.

    If not, could you please explain how to control this?

    Yes, the field is a textbox.  I suppose coding could be a workaround, but it doesn't seem right that I should have to figure out code for what should be a simple selection of the medium date format.  I would have to attach this code to every date field in my database, and there are quite a few.  I'm not prepared to go to that kind of effort.

    Wednesday, November 30, 2016 3:58 AM
  • Hi hitrumpsai,

    as we discussed we already informed you about the solutions and workarounds.

    I try to find a different work around for this issue but unfortunately I did not find any useful way.

    so you can again think about the options that we had suggested you in previous suggestions.

    you can give your feedback regarding this issue .

    if they get enough feedbacks for the same issue then they can try to add a new feature in Access for date format.

    Access User Voice

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 5:44 AM
  • Hi Deepak,

    Thanks for looking for a different workaround.

    Since there is no other new information, the thinking I did in the above post about the options previously suggested still stands.

    If anyone else has any bright ideas or revelations, I'm all ears.  Otherwise I'll request the new feature via feedback.  Not that that is really a solution, because it may or may not be implemented, and even if it is, it may not be very soon.

    Friday, December 2, 2016 9:15 AM
  • I have an Access 2013 form with a field in medium date format (eg. 29-Nov-16), but when I click on the field, the value re-displays in short date format (eg. 2016-11-29) (which ironically is longer and doesn't fit).  I've searched online and can't find any explanation.  How can I stop this behavior?

    Hi hitrumpsai,

    Did you check the Format property of the control on the form?

    Imb.

    Friday, December 2, 2016 10:53 AM
  • With input masks there is an option to store the data as masked or not. You must have chosen the masked option.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, December 2, 2016 4:27 PM
  • Hi lmb,

    Thanks for the thought. That was addressed in the 2nd and 3rd messages above.

    Friday, December 2, 2016 7:58 PM
  • Hi Bill,

    Sorry, I'm not following you.  Are you saying you can set an input mask, but some other property controls what it's used on?  If so, I'm not seeing what that property is. 

    There's four formatting aspects of the data: internal storage format, display format, input new value format, and editing existing value format.  Which one are you referring to?  My understanding of internal storage format is it's just a decimal number, an offset from a starting date, and not really relevant to this discussion.  My concern is the editing format.

    Friday, December 2, 2016 8:07 PM
  • Hi hitrumpsai,

    did you try to set the format by code as I mentioned earlier?

    I think the issue is only related with display the date in textbox when editing and you don't have any issue with saving the date with particular format.

    if code solve your issue then try to create a function with code and implement in all the fields.

    Regards

    Deepak  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 5, 2016 5:37 AM
  • Hi Bill,

    Sorry, I'm not following you.  Are you saying you can set an input mask, but some other property controls what it's used on?  If so, I'm not seeing what that property is. 

    There's four formatting aspects of the data: internal storage format, display format, input new value format, and editing existing value format.  Which one are you referring to?  My understanding of internal storage format is it's just a decimal number, an offset from a starting date, and not really relevant to this discussion.  My concern is the editing format.


    Caveat...I might be wrong as to the data formatting in the table. I haven't tested it as such. Yes the dates are still stored as numbers. When using an input mask you have the option to force a display that is the same as the input mask. On the other hand if the data type is a string you have the option to include the characters in the mask or not, too. Thus a mask of @@@-@@-@@@@ will store the hyphens as part of the data, not just a format if so chosen.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, December 5, 2016 3:34 PM