Query misses calculating table records, randomly.. (1 Viewer)

Punice

Registered User.
Local time
Today, 12:31
Joined
May 10, 2010
Messages
135
I have a form (frmState_Mileage), where a truck driver enters the odometer readings (M_Odom) for each leg of a trip. The form assigns a unique number (M_SerNo) to each leg's record, when a driver starts a new trip or continues on the same trip All records are saved in tblState_Mileage.

qryState_Mileage calculates the mileage traveled (M_Traveled) for each leg. It is the record source for a report, rptState_Mileage.

The problem that I have appears when I run the query, even though the data entered on the form is saved in the table*, accurately. Here is an example of the query in dataview that shows "what happens", starting with trip-leg 3.04 (no M-Traveled errors were encountered for le3gs 1.01 to 4.01)

M_SerNO* M_Odom* M_Traveled Remarks
3.04 429,204 60 Start of leg 3.04: M_Traveled = 429,264 - 429,204
3.05 429,264 94 Start of leg 3.05: M_Traveled = 429,358 - 429,264
3.06 429,358 Blank, here, is ok for start of a new trip.
4.01 429,358 94 It is calculated when a new trips starts.
4.02 429,452 This is the first instance of my problem: missing 62.
4.03 429,514 60 OK
4.04 429,574 94 OK
4.05 429,668 24 Ok
4.06 429,692 Missing 25
4.07 429,717 Blank, here, is ok for start of a new trip.
5.01 429,717 24 OK, start of a new trip
5.02 429,741 Missing 25
5.03 429,800 15 OK

I used this in qryState_Mileage to calculate M_Traveled. It worked perfectly for years 2019-2021.

M_Traveled: (Abs([tblState_Mileage]![M_Odom]-DLookUp("[M_Odom]","tblState_Mileage","([M_SerNo]-0.01)=" & [M_SerNo])))

NOTE: The problem was discovered in DB 2022, which was a copy of DB 2021 with the records deleted from the copied 2021 tblState_Mileage.
After entering the same records via the frmState_Mileage, the problem was still there. BUT, when I manually enter the records directly in the tblState_Mileage, the qryState_Mileage performs the correct calculations.

What the HELL is going on? I worked about 8 hours a day for two weeks, trying all sorts of 'solutions'. I'm 86 and wearing out.I even forgot what that '&' does. Now, it for the experts to solve. @#$^ computers.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:31
Joined
Feb 28, 2001
Messages
26,999
Question: What is the data type of M_SerNO? Your M_Traveled line suggests it is floating-point, either SINGLE or DOUBLE, but those numbers are susceptible to rounding/truncation errors because of the foibles of doing decimal fractional math on a binary machine. Also, the fact of it working with one way of data entry vs. a different way suggests that you look more closely at data entry methodology differences.
 

Punice

Registered User.
Local time
Today, 12:31
Joined
May 10, 2010
Messages
135
Question: What is the data type of M_SerNO? Your M_Traveled line suggests it is floating-point, either SINGLE or DOUBLE, but those numbers are susceptible to rounding/truncation errors because of the foibles of doing decimal fractional math on a binary machine. Also, the fact of it working with one way of data entry vs. a different way suggests that you look more closely at data entry methodology differences.
You nailed it, sir. As soon as I saw the work 'type', I realized what I should have done and did it. I had the 'number' configured to 'single, 2 decimal places. I attached the setup that works.

Puzzling though, is why did the previous two years db work correctly using the old setup.

Thanks for you quick reply. Saved by the forum, once again. Always learning, which is great.

Screenshot (4264).jpg
 

Attachments

  • 1649686015515.jpeg
    1649686015515.jpeg
    26.5 KB · Views: 211

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:31
Joined
Feb 28, 2001
Messages
26,999
Puzzling though, is why did the previous two years db work correctly using the old setup.

That gave me pause too, but you have what should now be a more stable solution, so maybe we should let the sleeping dog lie.

On the other hand, if there was an upgrade to a new version of Access, you would have probably changed the main libraries that do the math or that took a different view of the default format of something. The search for a potential cause opens up a can of worms and I wasn't planning on going fishing anytime soon. Hate to waste a perfectly good can of worms, after all...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Feb 19, 2002
Messages
42,970
I'm not sure why you chose Decimal as the data type. Most people don't realize this but Currency is a data type as well as a format. Currency is a scaled integer. That means that the decimal point is implied and the type is fixed at 4 decimal places. I don't remember its scale but for purposes of discussion, assume that the integer part of the number is limited to 9. So 123.45 would be stored as:
0000001234500. All arithmetic is integer arithmetic. For display purposes, the decimal is assumed to be 4 positions from the right which in this example is between the 3 and the 4.

You can display the Currency data type as standard, percent, currency, or however you want. The limitation is that you can't need more than 4 decimal places. The benefit is you avoid all those strange floating point errors where 1+ 1 might not = 2:)

I use Currency for ALL numeric values that don't require more than four digits of decimal precision.
 

Users who are viewing this thread

Top Bottom