Fix Access Time Fields Not Migrating to SharePoint
Question details
The user needs to correctly migrate time-only fields from a Microsoft Access database to SharePoint without losing the data.
- Product
- Microsoft Access and SharePoint
- Device & OS
- not provided
- Scenario
- Migrating a database containing time-only fields from Microsoft Access to SharePoint using the migration wizard.
- Observed behavior
- Time-only values do not migrate correctly or are omitted because Access stores them with a default hidden date that falls outside of SharePoint's supported date range.
Before making structural changes to your database or running update queries, ensure you have administrative rights to the destination SharePoint site and a complete backup of your Microsoft Access database.
Identify Underlying Dates and Use a Post-Migration Update Query
Change the Access field display format to General Date to spot unsupported hidden dates, then use an update query after the migration to correctly map the time values to SharePoint.
Microsoft Access always stores a date alongside any time value. If only a time is entered, Access defaults the hidden date to December 30, 1899. Because SharePoint has a different supported date range, these values are frequently omitted by the migration wizard.
Note that changing the field to 'Short Time' will not fix the issue, as this only alters how the data is displayed on your screen, not the actual stored underlying value.
Open your Microsoft Access table in Design View and change the format of the time field to 'General Date'.
Sort the field to easily identify any underlying dates that fall outside of SharePoint's supported date range (such as those defaulting to the year 1899).
Proceed with the standard Microsoft Access migration wizard to move your tables to SharePoint, knowing that some time fields may initially appear empty.
After the migration completes, open Access and create a new Update Query from the Create tab.
Join the newly linked SharePoint table with your original local Access table using the original unique ID, and update the SharePoint time fields with the correct Access values.
Try WPS Office for a Free, Lightweight Office Suite
While complex database migrations require specialized Microsoft tools, your daily document, spreadsheet, and presentation tasks don't have to be complicated or expensive. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, ensuring seamless transitions for your everyday work.
- 1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
- 2. Install the Software: Run the downloaded file and follow the simple on-screen instructions to install the suite.
- 3. Start Creating: Open WPS Office to instantly view, edit, or create documents with full Microsoft Office format compatibility.
Frequently Asked Questions
Why does my time-only field in Access show up as December 30, 1899?
Microsoft Access always stores a date with every time value. If you only type in a time, Access automatically defaults the hidden underlying date to December 30, 1899.
Will changing my Access field to 'Short Time' fix the migration issue?
No. 'Short Time' is merely a display format. It changes how the data looks on your screen but does not remove or alter the underlying 1899 date value stored in the database, meaning it will still fail SharePoint migration.
How does SharePoint handle the default 1899 dates from Access?
SharePoint has a different supported date range than Access. During migration, SharePoint may either omit these out-of-range values entirely or attempt to convert them to its own minimum date baseline, which is typically January 1, 1900.
