How to Fix the New Data Type That is Causing Problems to MS Access

[UPDATE: now with Access team link to fixes] Recently the Access team starting working on a new data type called Date & Time Extended, here’s Ebo Quansah, Access program lead:

Very soon, we will ship a new data type, known as Date & Time Extended, which enhances syntax compatibility with SQL while increasing accuracy & level of detail in date & time records.

While the feature is not yet enabled in Current Channel builds, most of the code for the feature is in the product in an inactive state. Nonetheless, we are aware of a problem that currently exists with this new code. As of today, if you are on version 2005, build 12827.20010 or greater, and you manipulate Decimal fields (Access DataType=Number/FieldSize=Decimal, or SQL DataType=Decimal) using DAO (Data Access Objects), you might have identified your app crashing. If you hover a variable with the type in VBA code, you may see that the field is not being displayed properly; either reading as ‘?????’ or giving a Type Mismatch error, or Access may crash.

We are very sorry for the inconvenience this causes in your Access app. The Access team is working on resolving this issue as soon as possible, and we will report back to you once this error has been fixed. In the interim, we’d recommend for you to roll back to a previous version (https://support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-clic), or switch to a slower channel (e.g Monthly Enterprise Channel, or SemiAnnual Channel), until the issue is fixed in Current Channel.  The issue only impacts Decimal types so if avoiding DAO code that manipulates Decimal types is possible, we’d advise this as a short term solution.

Thank you,

-Ebo Quansah

[UPDATE] See the Access teams fixes page on this

here

.

Here’s another way to fix the issue

Instead of rolling back, we’ve had success in solving the issue by changing the data type from Decimal to Double, we recommend testing the change using your beta platform first before going to production and make sure it doesn’t create any problems with your system. *Note: Using the double data type may cause rounding issues in your queries, you may wish to try using the Currency data type instead but our testing has not been completed as of this article using Currency.

Avoid this issues entirely by going Semi-Annual

We recommend to all our clients using the semi-annual update channel for their production systems, we simply don’t see the value in using a more frequent channel, in particular since you would have avoided this issue along with others in the last 6 months. Click here to learn how to change your channel.

Speaking of channels, Microsoft has changed their names

Here’s more info on the new channel names. Regardless of the name, pick the update channel that is every six months: Semi-Annual Enterprise Channel. (Channel names with the word Preview in them are to be used by your IT Department to test before it’s rolled out to the rest of the organization).