none
Date format Question RRS feed

  • Question

  • I have a Access 2013 application front end, that is linked to a SQL server Back End.  The sql Field is called POPUpDate and is a SQL Date Field.

    The access control to input data is similar, but my input mask is 99/99/0000;0;" "

    When I input data, it presents itself as I expect, MM/DD/YYYY.  However when I input the data, it stores and subsequently presents itself as for example, 2019-12-12, which is not the format I desire.  How can I get this to store/present as 12/12/2019?

    Wednesday, May 15, 2019 1:31 PM

Answers

  • Check your computers Region and Language settings. Your date formats may be defaulting to your computers Short Date setting. You can format a date on a report or form or a query using the Format function. For example: Format([POPUpDate],"mm/dd/yyyy").
    • Marked as answer by tkosel Wednesday, May 15, 2019 3:39 PM
    Wednesday, May 15, 2019 3:00 PM
  • The new field name NoteExpirationDate cannot have a table name indicated. Delete the fields table name (IPFeatureChild).
    • Edited by Lawrence Ellefson Wednesday, May 15, 2019 10:18 PM
    • Marked as answer by tkosel Wednesday, May 15, 2019 10:30 PM
    Wednesday, May 15, 2019 10:15 PM
  • To all,

    I discovered what the issue is, why I couldn't get it to work and why it was so difficult to experiment to solve it.  I don't know why, but on a whim, I changed the SQL Server table, field data type from Date to DateTime.  All my problems went away.

    • Marked as answer by tkosel Wednesday, May 22, 2019 9:26 PM
    Wednesday, May 22, 2019 9:26 PM

All replies

  • Check your computers Region and Language settings. Your date formats may be defaulting to your computers Short Date setting. You can format a date on a report or form or a query using the Format function. For example: Format([POPUpDate],"mm/dd/yyyy").
    • Marked as answer by tkosel Wednesday, May 15, 2019 3:39 PM
    Wednesday, May 15, 2019 3:00 PM
  • Thanks Lawrence!!

    Wednesday, May 15, 2019 3:39 PM
  • Lawrence,

    I thought I had it but guess not.

    The control source is "IpPopUpNoteExpirationDate" and it yields the following.

    I I change the control source to "=Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy")"  I get the following.

    What am I doing wrong?

    Wednesday, May 15, 2019 8:07 PM
    1. Change the query field name FROM: IpPopUpNoteExpirationDate TO: NoteExpirationDate: Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy")
    2. Change the control source in the form to NoteExpirationDate

    You can change a query field name to anything you wish as long as it has a colon after it with the formatting following the colon. Hense:

    NoteExpirationDate: Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy")

    But you CAN'T do: IpPopUpNoteExpirationDate: Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy") because ACCESS will give a circular reference error.

    Wednesday, May 15, 2019 8:35 PM
  • Lawrence, again thanks but I cannot get it to work.

    Wednesday, May 15, 2019 9:51 PM
  • The new field name NoteExpirationDate cannot have a table name indicated. Delete the fields table name (IPFeatureChild).
    • Edited by Lawrence Ellefson Wednesday, May 15, 2019 10:18 PM
    • Marked as answer by tkosel Wednesday, May 15, 2019 10:30 PM
    Wednesday, May 15, 2019 10:15 PM
  • Thanks Lawrence,

    I guess I should trust Access more, as it deleted the table name when I edited it as  you indicated.  Like an idiot, I put it back.

    It is certainly smarter than me!

    Wednesday, May 15, 2019 10:30 PM
  • lawrence,

    Thought I had this issue solved, but found a issue.  If I use format to get the date to appear like I desire it, I cannot edit it.  I replaced the objects control source with: =Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy")

    It displays the existing data properly, but I cannot enter new dates or edit the existing.  Perhaps I just need to train my users to properly interpret the data in the format that sql server wants to present it.

    Sunday, May 19, 2019 6:52 PM
  • Don't change the control source. The formatting needs to done in the query itself and the control source needs to be bound to the field 'NoteExpirationDate' in the query (if you used the name in my example). Open the query in Data Sheet View to confirm the field is showing properly and that you can edit data.

    Sunday, May 19, 2019 8:03 PM
  • Lawrence,

    1. Change the query field name FROM: IpPopUpNoteExpirationDate TO: NoteExpirationDate: Format([IpPopUpNoteExpirationDate],"mm/dd/yyyy")
    2. Change the control source in the form to NoteExpirationDate

    You can change a query field name to anything you wish as long as it has a colon after it with the formatting following the colon. Hense:

    In trying to understand your previous post, I thought I did and the display of the date was correct.  However how can the "NoteExpirationDate" be valid for updating as it is not bound to anything?

    Sunday, May 19, 2019 10:00 PM
  • You wrote:

    "I have a Access 2013 application front end, that is linked to a SQL server Back End.  The sql Field is called POPUpDate and is a SQL Date Field.

    The access control to input data is similar, but my input mask is 99/99/0000;0;" "

    When I input data, it presents itself as I expect, MM/DD/YYYY.  However when I input the data, it stores and subsequently presents itself as for example, 2019-12-12, which is not the format I desire.  How can I get this to store/present as 12/12/2019?"

    You are correct in that the date will not be editable. I have been doing some date formatting experiments on a database of my own and found that if you need the dates to be editable on a form, then the field in the query cannot be changed as I suggested. It needs to be as you originally had it with the field name left as IpPopUpNoteExpirationDate and table name as IPFeatureChild.

    Also, I guess I am confused about the following:

    1. How is the IpPopUpNoteExpirationDate field formatted in the IPFeatureChild query (or is IPFeatureChild a table?).
    2. Is the IpPopUpNoteExpirationDate a Date/Time Data Type?
    3. How does the POPUpDate field fit into all this? And is this the field you are trying to enter a date into?

    If a table field has a Data Type of Date/Time, then ACCESS will automatically default to your systems format of yyyy-mm-dd. You can't change that as far as i know. What you can do is present a date as a different format ("mm/dd/yyyy" for exampe), but you can't edit that field using that format.

    You can change the field format in your table to a TEXT Data Type instead of DATE/TIME Data type and then you can enter data in a mm/dd/yyyy format and the table will store it as such BUT it is not a Date as far as ACCESS is concerned so you can't do the same things you could with a Date Data Type.

    Monday, May 20, 2019 3:23 PM
  • Lawrence,

    Thanks  for you help.  I have been out of pocket for a while, and am sorry I didn't respond sooner.

    You said:

    Also, I guess I am confused about the following:

    1. How is the IpPopUpNoteExpirationDate field formatted in the IPFeatureChild query (or is IPFeatureChild a table?).
    2. Is the IpPopUpNoteExpirationDate a Date/Time Data Type?
    3. How does the POPUpDate field fit into all this? And is this the field you are trying to enter a date into?

    IPFeatureChild is a Linked table, SQL Server.  The IpPopUpNoteExpirationDate field is  a Date data type, not Date/Time.  The POPUpDate  field, was an attempt to make the date usable the way I wanted it, not really needed.  I can input theIpPopUpNoteExpirationDate as "mm/dd/yyyy" , but when I come back to the form later, it is back to the format of yyyy-mm-dd

    It looks as if I will be forced to train my users to get used to the date format of yyyy-mm-dd

    Wednesday, May 22, 2019 8:44 PM
  • To all,

    I discovered what the issue is, why I couldn't get it to work and why it was so difficult to experiment to solve it.  I don't know why, but on a whim, I changed the SQL Server table, field data type from Date to DateTime.  All my problems went away.

    • Marked as answer by tkosel Wednesday, May 22, 2019 9:26 PM
    Wednesday, May 22, 2019 9:26 PM