Need an error trap for Date/Milage

External Since: Sep 29, 2008 Posts: 3

Part of my Access 2003 DB tracks Vehicle Events and records a date and
an odometer reading along with the Vehicle registration

Vehicle Table contains VehicleNumber
Driver Table contains DriverName
Maintenace Table contains JobNumber
Odometer Table contains 'UnitNum', 'Date' and 'Odometer' and is linked
to the Vehicle Table

Data is entered at the time it is received from the various paperwork
sources, consequently it is stored in random order.

LineID UnitNum Date Odometer
1 Q-27 1/01/08 5000
2 Q-27 3/01/08 6048
3 Q-27 2/01/08 5407
4 Q-27 6/01/08 6503
5 Q-27 4/01/08 6201
6 Q-27 2/01/08 5534
7 Q-27 2/01/08 5592
8 Q-27 1/01/08 5117
9 Q-27 2/01/08 5609
10 Q-27 3/01/08 5983
11 Q-27 1/01/08 5613 This entry has an error
12 Q-27 7/01/08 6569
13 Q-27 3/01/08 5739
14 Q-27 4/01/08 6447
15 Q-27 7/01/08 6662
16 Q-27 2/01/08 5288

There has been an error in the data entered which shows up if the data
is sorted by date or by Odometer, but is not yet obvious to the eye.
Below is the data sorted by each.

DATA SORTED BY DATE:

LineID UnitNum Date Odometer
1 Q-27 1/01/08 5000
8 Q-27 1/01/08 5117
11 Q-27 1/01/08 5613 Odometer out of order
3 Q-27 2/01/08 5407
6 Q-27 2/01/08 5534
7 Q-27 2/01/08 5592
9 Q-27 2/01/08 5609
16 Q-27 2/01/08 5288
2 Q-27 3/01/08 6048
10 Q-27 3/01/08 5983
13 Q-27 3/01/08 5739
5 Q-27 4/01/08 6201
14 Q-27 4/01/08 6447
4 Q-27 6/01/08 6503
12 Q-27 7/01/08 6569
15 Q-27 7/01/08 6662

DATA SORTED BY ODOMETER:

LineID UnitNum Date Odometer
1 Q-27 1/01/08 5000
8 Q-27 1/01/08 5117
16 Q-27 2/01/08 5288
3 Q-27 2/01/08 5407
6 Q-27 2/01/08 5534
7 Q-27 2/01/08 5592
9 Q-27 2/01/08 5609
11 Q-27 1/01/08 5613 Date out of order
13 Q-27 3/01/08 5739
10 Q-27 3/01/08 5983
2 Q-27 3/01/08 6048
5 Q-27 4/01/08 6201
14 Q-27 4/01/08 6447
4 Q-27 6/01/08 6503
12 Q-27 7/01/08 6569
15 Q-27 7/01/08 6662

(The error here was the Odometer should have been 5163). I would like to
trap these errors at the time of entry and causes the conflict. I think
that something like a comparison of the odometer relative to existing
entries should then check that the date would be in the correct
chronological order. Any suggestions how I would go about this?

Many thanks
ROB

External Since: Nov 15, 2007 Posts: 1133

Rob <dy.DeleteThis@woodyallan.biz> wrote:

>Part of my Access 2003 DB tracks Vehicle Events and records a date and
>an odometer reading along with the Vehicle registration

Interesting because I'll be implementing similar logic in a few weeks in my Granite
Fleet Manager www.granitefleet.com.

>(The error here was the Odometer should have been 5163).

One approach would be to read the previous and next records in chronological order
and see if the data is within those bounds. Now if it's earlier or greater than any
other records then you should think about reasonableness bounds check. That is is
it under, say 1000 or 5000 miles difference than the last entry. I'd put those
bounds in a Global Options table so the user can muck with them accordingly.

This error could just as easily have been a wrong date. Now the data anamoly would
likely look pretty much the same but you need to think about what if the data got
somehow entered as 1908 or 2108, that is the top or the bottom.

Now there likely are means of doing bloody complex queries to figure all this out.
But the reality is that a function call with various recordset queries might be best
as it will be much more readable.

Also what happens if the user insists that yes, this is reasonable?

If you have any such you will need to also consider hours accumulated which, for
example, bulldozers do.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/