how to format ms access dates with odbc data source

Nick Coe 0 Reputation points
2024-03-20T14:07:09.7366667+00:00

M365 Access App FE on PC win 11 - SQL Server Express back end on server.
Can't format dates in form txt controls bound to appropriate date field from BE.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,743 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
822 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 48,486 Reputation points
    2024-03-20T14:14:16.71+00:00

    The format that SQL returns isn't important provided it returns it as a date. Then you can format the data on the frontend which is where formatting should happen. Unfortunately you haven't really provided us any details to help you further. Can you show us what format the SQL query you're running is returning, how you are binding it to your Access front end and what format settings you're using in your text control?

    0 comments
  2. Albert Kallal 4,651 Reputation points
    2024-03-21T18:09:10.0133333+00:00

    One thing to double check before you start "chopping" away at the Access forms and code?

    Open up a linked table in design mode. (ignore the warning about read only), and double, triple check that Access currently sees the datetime columns as in fact datetime and NOT text columns.

    This simple little check can save you boatloads of work. If Access does not see the datetime columns in the linked table as datetime, then all of your forms will of course then show date's as text, and you forever be chasing down bugs and issues.

    After a migration to SQL server, you in fact should NOT be having issues with datetime formatting, but you WILL have issues if Access does not see those columns as datetime.

    The issue of course is the ODBC driver you choose when linking tables.

    If you choose the older "legacy" SQL driver then Access will see SQL datetime2 columns as text.

    So you can either:

    Change all datetime2 columns in SQL server to datetime, or you can use a "newer" SQL driver with Access. Of course while using the newer ODBC driver is the preferred fix for above, you have to install that newer SQL odbc driver on each work station.

    So, before you go on a big "wild goose" chase of trying to fix the formatting of datetime columns? Do the above quick test of looking at a linked table in design mode in Access, and be sure that Access "sees" the datetime column as a datetime type column. fix this issue, and near all other issues will be fixed, and without you having to do much of anything.

    0 comments