none
Date format for Ms Access frontend & Sql server backend RRS feed

  • Question

  • Hi Everyone! I have newly build ms access programs frontend and sql server backend. My date datatype format change into (yyyy-mm-dd) when do migration. Anyone have idea how to change my frontend date format  it become(ms access forms) (dd-mm-yyyy). Or change permanently my sql date datatype into (dd-mm-yyyy). I hope anyone who can help me. thank you

    Wednesday, June 7, 2017 6:49 AM

All replies

  • One VERY important detail to check and fix BEFORE you start messing with the application is to open a table in DESIGN mode. Due to being a linked table, then ignore the message about the table being read only.

    Check the date columns and ensure they are seen as date time. I going to STRESS that you double check this issue. If the columns are seen as string/text, then you need to re-link the tables to fix this.

    The SSMA migration wizard will up-size columns as datetime2, and the STANDARD SQL driver from Access will THEN return such columns as string/text. The result is date data like you have. You do NOT want to attempt any fix to your application until such time you resolve this issue.

    Recommend fixes to above are:

    Rename the columns from datetime2 back to the type of datetime in SQL server. Now re-link the access tables and your date formatting issue will go away. I only recommend this idea if you have 1 or two tables and a few datetime columns. Otherwise this can be LOTS of work if you have lots of tables.

    Re-link the tables using a newer SQL odbc river (the native 11 driver is a good choice). If you link using the default “legacy” ODBC driver then datetime2 columns will appear as text. I seen someone make 3 days of messy changes to their application to then AFTER realize the issue was not date formatting, but the fact that access was seeing and viewing those columns  as text. So try re-linking tables using native 11. Native 11 is a good choice, but you have to install the native 11 on each workstation. Because of this “hassle” of installing on each workstation, then I for now continue to use the standard SQL driver to avoid having to install native 11 SQL driver on each workstation. this also means by choice I avoid the datetime2 data column in sql server.

    If you have lots and lots of tables, and you do NOT want to adopt the native 11 sql driver then you need to re-migrate your data (or manually change each datetime2 column to datetime). If you are using SSMA to migrate this data, then you can/should change the data type mapping from datetime2 to datetime. That way after I migration of many tables the datetime columns will be correct for the default odbc SQL drivers.

    Just looking at your screen shot you can see the “–“ for that date field delimiters. That looks VERY wrong and suggests the date column is being seen as TEXT and not a datetime column.

    I don’t want to sound un-kind, but do not pass go, do not pass jail, don’t roll the dice for the next move on the monopoly game board.

    You want to 100% fix this column data type issue before you go on a wild goose hunt modifying tons of forms and code and all kinds of things in Access when you simply don’t need to nor want to.

    Migration to SQL server can and will introduce issues and all the work you need and want! However a wide spread break down of date formats is not something you have to change nor deal with after a migration. Things should be working as before without changes to the access application in regards to date formatting.

    Good luck!

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, June 8, 2017 3:52 AM
  • On the UI side you can use an Input Mask:

    https://support.office.com/en-us/article/Control-data-entry-formats-with-input-masks-e125997a-7791-49e5-8672-4a47832de8da

    https://support.office.com/en-us/article/Add-and-customize-date-and-time-formats-763b9b04-ccf8-46a8-9ee3-6d8f6fb9e2f8

    You may also want to incorporate the DatePicker with the TextBox.

    Dates are stored as numeric data types so there is no formatting on the database side.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 8, 2017 12:41 PM