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