A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access crashes on query parsing NMEA (GPS) data



 
 
Thread Tools Display Modes
  #1  
Old June 18th, 2008, 01:33 PM posted to microsoft.public.access.queries
J.T. van der Wal
external usenet poster
 
Posts: 1
Default Access crashes on query parsing NMEA (GPS) data

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
Event Viewer-logs.

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
writing.
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.

Please fix.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.