Type Mismatch ---traced to an issue with Decimal fields in Microsoft Access (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Jan 23, 2006
Messages
13,409
A heads up!
Update 2005 12827.20268 (released June 2, 2020) appears to be causing various problems with Decimal fields in Microsoft Access databases.
I just noticed this on another forum and posting this link to an ongoing review. The M$oft response is dated 2020-06-08 .
 

Minty

AWF VIP
Local time
Today, 17:54
Joined
Jul 26, 2013
Messages
7,571
@jdraw Thank you for the information.

Interesting explanation!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:54
Joined
Feb 28, 2001
Messages
18,393
It seems that the MS folks are getting ready to introduce a bit more precision into date/time fields - perhaps related to UTC compliance? I can tell you that a DOUBLE fraction (of which DATE is a typecast) has sufficient precision to hold times accurate to the millisecond. I know this because OpenVMS has a similar approach to DATE information. They use a 64-bit integer based on a rather complex internal high-speed crystal clock that measures time in JIFFIES (= 0.1 microseconds). I know the hardware of at least the IA64 Intel architecture can do this because OpenVMS runs on both the IBM Xeon (a 64-bitter) and the IA64 Itanium server. The problem with the current Access time system is that even the Timer() function only goes to 1/60th of a second. But if you use UTC, that often goes down to the millisecond according to the specification for UTC and Time Synch services.

EDIT: Inferred this from the comments in the line provided by jdraw.
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 11:54
Joined
Apr 3, 2020
Messages
571
good catch, Jack

I saw that message from Ebo too -- good that Daniel shared it publicly.

Decimal data type has always been an issue -- while you can use them in your table design and linking, there is no data type for them in VBA, except to use variant.

Richard, for me, the problem with double-precision numbers (what date-time uses) is that they *aren't* precise! Internally, they're floating point, which means that equal to (=) working properly is iffy, so better to use comparisons like (expression which might contain ABS) is greater than or equal to or less than or equal to.

The Timer() function provides better granularity -- and can be used to determine fractions of a second -- but also, single is a floating point number -- so not good for linking or exact comparison
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 11:54
Joined
Feb 28, 2001
Messages
18,393
Crystal, not what I meant.

DOUBLE gives you a 53-bit mantissa. The time-line method used by Access and Excel treats time (in the abstract) as a linear domain in units of days and allowing fractions thereof. The integer portion of days allows easy conversion of a number of days to a date based on the Epoch date. This algorithm has been known since the 1960s at least. Maybe longer. The fractional approach allows you to convert time as a fraction of a day. But if you look at the storage requirements, ...

The current date is somewhere in the 40,000s range, which requires no more than 16 bits ( < 64K days). A time requires 86,400 discrete ticks to represent seconds, which is no more than 17 bits ( < 128K seconds.) That's 33 bits consumed by date and time to the second. You've got 20 bits left over in the mantissa. UTC requires you to reach millisecond timing when doing certain precise networking operations, such as Network Time Protocol. So change seconds to milliseconds and you consume another 10 bits (=1K) of that mantissa. You STILL have 10 bits to help with and guard against the inevitable rounding associated with fractions that contain odd-number factors.

My comment is merely that based on the idea of a Date/Time Extended data type, they might get by with a new set of representational routines, like an extended time code to show fractions of a second. I wonder if they are going to tie into the high-precision internal clock that has been on Intel systems since the advent of the Gigahertz backplanes. I've looked it up - the fast clock IS present in WinServer 2000 and Windows Vista and later.


You are worried about rounding issues and I get that. But my comments were about capacity, not representation. The way OpenVMS does it is not significantly different and that is a server-class O/S. They use a 64-bit integer with units of JIFFIES (=0.1 microsecond). But the problem is always the same. Past a certain point, you get lost in the low-level bits WHATEVER you choose as a representation. Which is why I agree that you need some type of bounding function - like ROUND() or FIX() or perhaps convert it to a string date and compare in yyyy-mm-dd hh:nn:ss format, where the strings would compare correctly for equal, less, or greater.
 
Last edited:

Users who are viewing this thread

Top Bottom