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

Rob <dy (AT) woodyallan (DOT) biz> wrote:

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

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/

Use the BeforeUpdate event procedure of the *form* (not controls) to look up
the Odometer table to see if:
a) the most recent prior reading was higher, or
b) the next subsequent reading that is lower.

By looking only that the closest dates you don't create problems if an
odometer (or motor or whatever) is replaced at some point.

DLookup() is not up to scratch for this, so grab the ELookup() function from
here:
http://allenbrowne.com/ser-42.html
This lets you specify a sort order so we can define which value to return.

There's an example event procedure below. Since it returns you the previous
and next date's entries, it would be easy enough to modify it to include a
warning if the distance travelled since the previous entry is unlikely.

If you ever need to get the next/previous row's value in a query, a subquery
could do that for you. Here's an example:
http://allenbrowne.com/subquery-01.html#AnotherRecord
Since it's based on a meter-reading, it's very similar.

BTW, the field name Date is likely to cause you problems. I've tried to
craft the code to avoid those issues, but a better solution would be to
rename it to something else, e.g. TripDate. There are actually thousands of
field names that can cause you grief. Here's a list to refer to when
designing tables:
http://allenbrowne.com/AppIssueBadWord.html


Sample code (untested: needs debugging):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varResult As Variant
Dim strWhere As String
Dim strMsg As String
Dim bWarn As Boolean
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.UnitNum) Then
Cancel = True
strMsg = strMsg & "UnitNum required." & vbCrLf
End If
If IsNull(Me.[Date].Value) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Odometer) Then
Cancel = True
strMsg = strMsg & "Odometer required." & vbCrLf
End If

If Cancel Or ((Me.UnitNum = Me.UnitNum.OldValue) And _
(Me.[Date].Value = Me.[Date].OldValue) And _
(Me.Odometer = Me.Odometer.OldValue)) Then
'Do nothing
Else
'Higher previous entry?
strWhere = "(UnitNum = """ & Me.UnitNum & """) AND (Odometer.[Date] <= "
& _
Format(Me.[Date].Value - 1, strcJetDate) & ")"
varResult = ELookup("Odometer", "Odometer", strWhere, "Odometer.[Date]
DESC")
If varResult > Me.Odometer Then
bWarn = True
strMsg = strMsg & "Previous date's entry was " & varResult & "." &
vbCrLf
End If
'Lower later entry?
strWhere = "(UnitNum = """ & Me.UnitNum & """) AND (Odometer.[Date] >= "
& _
Format(Me.[Date].Value + 1, strcJetDate) & ")"
varResult = ELookup("Odometer", "Odometer", strWhere, "Odometer.[Date]")
If varResult < Me.Odometer Then
bWarn = True
strMsg = strMsg & "Next date's entry is " & varResult & "." & vbCrLf
End If
End If

If Cancel then
strMsg = strMsg & vbCrLf & "Correct the entry, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Cannot save record"
ElseIf bWarn Then
strMSg = strMsg & vbCrLf & "Proceed anyway?"
If MsgBox strMsg, vbYesNo+vbDefaultButton2+vbQuestion, "Are you sure?")
<> vbYes Then
Cancel = True
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rob" <dy (AT) woodyallan (DOT) biz> wrote

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> replied in message
news:g075f4pkmg4uc5hk0ohdjfcaeph6ff963f (AT) 4ax (DOT) com...
Tony, I did one of these recently, and asked the client, "Could there ever
be a case where you need to store both (i.e. hours of operation and miles
travelled)? The answer was no, so we just called the field MeterReading
(Double), and added a MeterTypeID field to the VehicleType table.

The real fun comes when you have to project anticipated servicing dates
based on current usage trends, where service is required every xx
hours/miles or xx months/days whichever comes first, particularly if
servicing is component-based (some components are different to others)
and/or tiered (e.g. the A service includes all requirements of the B
service.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <AllenBrowne (AT) SeeSig (DOT) Invalid> wrote:

Double? That's a big extravagent. <smile>

However there can be case where a truck, that spends both a great deal of time
driving and a great deal of time providing hydraulic or pumping power where the next
service depends on either miles/kms or hours. A bad example would be a firetruck
although they typically don't drive much and spend a lot of time on scene pumping
water. A good example might be a underground boring machine on the back of a truck
which drives many hundreds of kms to it's next stop but spends hours providing
hydraulics and power to the boring machine.

Then there are situations where there's another motor on the same machine. For
example a large truck or front end loader mounted snowblower with one motore for the
drive axles and another motore for the snow blower. But for those few situations
the user just creates two units. Especially if it's a snow blower on a front end
loader which could be mounted on different loaders from year to year.

Oh yes. And I'm not going to get into projecting anticipated dates for now. It can
very too much up here what with winter being a major factor. The bulldozers have
been running 12 hours a day since May and will be shutting down in another few or
four weeks in this area. At peak times the service intervals may only be 10 to 20
days apart and then you get rained out for a week.

I will be giving them the ability to
1) easily locate the units for they which haven't received a odometer or hour meter
reading in some time
2) print a report by unit type with underlines so a gofer (person who "goes for" this
and that.) can go around to the units and write down the readings and a simple means
of entering that data.
3) reminders when they are approaching or have exceeded the various service intervals
such as, in gasoline motors, the timing chan.

One amusing anecdote as the the New York ambulance doesn't bother scheduling major
maintenance on their ambulances. They are, on average, in a major accident every 18
months and written off.

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/

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote:

Actually maybe not. While unlikely a Single would only take you up to 3,402,823 kms
so that's certainly possible.

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/

I rarely use single anyway. Even for calculating fractions of an hour, the
inaccuracies are cumulative.

As per your other reply, the scenarios are always fun.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Toews [MVP]" <ttoews (AT) telusplanet (DOT) net> wrote