I have had a problem parsing a time-fields contained in NMEA-data
(record-type: $GPRMC) in an Access-query.
My original data source neatly had output every second, nicely matching the
maximum accuracy of time-keeping in Access (date/time-fields). The problem
started occurring when a different GPS was used and the time-information
included 1/100th of a second.
I found myself forced to split the original string, separate hours, minute,
and seconds (including decimal values for that seconds). After that I
rounded the seconds, only to find myself in the next troublespot.
This time the capabilities of Access in interpreting a time-string where the
limiting factor. Rounding the seconds to the nearest integer, causes a
problem for that final half a second of a minute: 59.51 and above round to
60. And 60 is not a valid input value (59 and below are).
My work around this was to calculate a real (double) as follows: hours/24 +
minutes/(24*60) + seconds/(24*60*60). This yields a fractional value for the
time past during the day, just as Access uses to store in a date/time field.
Using Hour(); Minute() and Second(), gave me the proper parts to rebuild the
final proper time using TimeSerial( Hours; Minutes; Seconds).
Yet even than the final step combining the correct time with the date in one
date/time-field would still crash Access. The error-messages shown on-screen
are not helpfull in diagnosing the problem. Nor is the data contained in the
A company-internal guru from the IT-service desk, suggested that I may have
been overdoing the stacking off queries on top of each other. As work-around
the following works, store the results not as a query but as table and
proceed from the new table. Much more cumbersome, but it works.
Please note that this looks a lot like the problems that were supposedly
fixed by Service Pack 6 for the MS-Jet4.0-engine (which had the following
version number: 4.0.6218.0 ). Observe that this problem happened with
4.0.9511.0, a lot higher and the newest available for WinXP at the time of
MS-Jet 4 SP6 in a.o. mentioned in kb 309163.
Getting the newest MS-Jet-engine is a topic covered in kb 239114.
Clearly and old problem has reared it's head again.