Compare Data

[Guest]

I have a table for vehicles and a table for trip details. Every day, the
vehicle gets used, and the user must enter the odometer reading before the
route, and the odometer reading when the vehicle is brought back. The next
day, the process is repeated. A lot of times, the user puts the wrong
odometer reading at the start of the trip. If the reading when the vehicle is
brought back the day before is 7000, and the user enters a number less than
7000 at the start of the next day's route, obviously it is a mistake, because
the number has to at least be what the ending number was the day before. If
this happens, is there a way I can have a message pop up to say that "The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Guest]

I tried the first one, and if I put the correct number in, it is still giving
me the message box. However, if I put a very large number in, it doesn't give
me the message. I am thinking that maybe it is looking at ANY odometer
reading, and not the particular bus' reading. Should there be a place I put
in the bus ID as well?

Too bad that the table validation rules won't allow using DMax.

If entering data by a form, and you should, the following code will work.
You need to change it to the proper field and table names:

Private Sub OdoStart_BeforeUpdate(Cancel As Integer)
If Me.OdoStart < DMax("[OdoStop]", "Trips") Then
MsgBox "The Odometer Number Entered is Incorrect"
End If
End Sub

This one will prevent someone from putting in an invalid stop.

Private Sub OdoStop_BeforeUpdate(Cancel As Integer)
If Me.OdoStop < Me.OdoStart Then
MsgBox "The Odometer Number Entered is Incorrect"
End If
End Sub
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table for vehicles and a table for trip details. Every day, the
vehicle gets used, and the user must enter the odometer reading before the
route, and the odometer reading when the vehicle is brought back. The next
day, the process is repeated. A lot of times, the user puts the wrong
odometer reading at the start of the trip. If the reading when the vehicle is
brought back the day before is 7000, and the user enters a number less than
7000 at the start of the next day's route, obviously it is a mistake, because
the number has to at least be what the ending number was the day before. If
this happens, is there a way I can have a message pop up to say that "The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Allen Browne]

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box on
your form to DLookup() the highest value so far for that vehicle, and give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Guest]

My mistake! I forgot the obvious. Watch out for word wrapping below. VIN
needs to be the name of the field in the table that identifies a vehicle.

In "Forms!Orders!VIN" Orders needs to be the name of the form and VIN needs
to be the name of the text field on the form that identifies the vehicle.

Private Sub OdoStart_BeforeUpdate(Cancel As Integer)
If Me.OdoStart < DMax("[OdoStop]", "Trips", "[VIN] = " &
Forms!Orders!VIN ) Then
MsgBox "The Odometer Number Entered is Incorrect"
End If
End Sub

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Both fields are numbers. As long as I enter a number larger than the highest
odostop for ANY vehicle, it allows me to enter the number. Otherwise, if I
enter a number LOWER than the highest odostop, I get the error message, even
though that particular vehicle number's odostop is lower. There are
approximately 350 vehicles.

Strange. Are the odo fields text or number datatype?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I tried the first one, and if I put the correct number in, it is still giving
me the message box. However, if I put a very large number in, it doesn't give
me the message. I am thinking that maybe it is looking at ANY odometer
reading, and not the particular bus' reading. Should there be a place I put
in the bus ID as well?

:

Too bad that the table validation rules won't allow using DMax.

If entering data by a form, and you should, the following code will work.
You need to change it to the proper field and table names:

Private Sub OdoStart_BeforeUpdate(Cancel As Integer)
If Me.OdoStart < DMax("[OdoStop]", "Trips") Then
MsgBox "The Odometer Number Entered is Incorrect"
End If
End Sub

This one will prevent someone from putting in an invalid stop.

Private Sub OdoStop_BeforeUpdate(Cancel As Integer)
If Me.OdoStop < Me.OdoStart Then
MsgBox "The Odometer Number Entered is Incorrect"
End If
End Sub
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a table for vehicles and a table for trip details. Every day, the
vehicle gets used, and the user must enter the odometer reading before the
route, and the odometer reading when the vehicle is brought back. The next
day, the process is repeated. A lot of times, the user puts the wrong
odometer reading at the start of the trip. If the reading when the vehicle is
brought back the day before is 7000, and the user enters a number less than
7000 at the start of the next day's route, obviously it is a mistake, because
the number has to at least be what the ending number was the day before. If
this happens, is there a way I can have a message pop up to say that "The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID, and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Jamie Collins]

Out of interest, why would you want to use DMAX in this case? Surely
odo_start must be greater than *all* prior readings? e.g. the
following table-level CHECK constraint, which uses no aggregation,
seems to work:

CREATE TABLE Trips (
bus_ID INTEGER NOT NULL,
trip_date DATETIME NOT NULL,
UNIQUE (bus_ID, trip_date),
odo_start INTEGER NOT NULL,
CHECK (odo_start >= 0),
odo_stop INTEGER NOT NULL,
CHECK (odo_start < odo_stop),
CHECK (NOT EXISTS (
SELECT *
FROM Trips AS T1, Trips AS T2
WHERE T1.bus_ID = T2.bus_ID
AND T1.trip_date < T2.trip_date
AND T1.odo_start > T2.odo_start
))
);

Jamie.

--

 

[Guest]

Allen and Jerry: I tried both of your ideas, but I keep getting a Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the field
names etc. to match mine, but it kept coming up with an error. With Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

 

[Allen Browne]

If VIN is a Text field (not a Number field in table design), you need extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen and Jerry: I tried both of your ideas, but I keep getting a Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using
both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box on
your form to DLookup() the highest value so far for that vehicle, and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Guest]

Yes, it is a text field. I cut and pasted your code, and when I put my curser

If VIN is a Text field (not a Number field in table design), you need extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen and Jerry: I tried both of your ideas, but I keep getting a Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using
both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box on
your form to DLookup() the highest value so far for that vehicle, and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a table for vehicles and a table for trip details. Every day, the
vehicle gets used, and the user must enter the odometer reading before
the
route, and the odometer reading when the vehicle is brought back. The
next
day, the process is repeated. A lot of times, the user puts the wrong
odometer reading at the start of the trip. If the reading when the
vehicle
is
brought back the day before is 7000, and the user enters a number less
than
7000 at the start of the next day's route, obviously it is a mistake,
because
the number has to at least be what the ending number was the day
before.
If
this happens, is there a way I can have a message pop up to say that
"The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID,
and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Allen Browne]

Sounds like you pasted in in the code window?

It was intended for the Control Source property of a text box.

If you want to put it in code, add the name of the control you want to
assign it to before the =.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes, it is a text field. I cut and pasted your code, and when I put my
curser
on the next line, an error message, "Compile Error: Expected: Line number
or
label or statement or end of statement. I wonder what I'm doing wrong.....

If VIN is a Text field (not a Number field in table design), you need
extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

Allen and Jerry: I tried both of your ideas, but I keep getting a
Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the
field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using
both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

:

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box
on
your form to DLookup() the highest value so far for that vehicle, and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a table for vehicles and a table for trip details. Every day,
the
vehicle gets used, and the user must enter the odometer reading
before
the
route, and the odometer reading when the vehicle is brought back.
The
next
day, the process is repeated. A lot of times, the user puts the
wrong
odometer reading at the start of the trip. If the reading when the
vehicle
is
brought back the day before is 7000, and the user enters a number
less
than
7000 at the start of the next day's route, obviously it is a
mistake,
because
the number has to at least be what the ending number was the day
before.
If
this happens, is there a way I can have a message pop up to say that
"The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID,
and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Guest]

I'm sorry....I'm not sure what you mean...in your first post you said to put
it in the beforeupdate or afterupdate property. Then where do I put the
message?

Sounds like you pasted in in the code window?

It was intended for the Control Source property of a text box.

If you want to put it in code, add the name of the control you want to
assign it to before the =.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes, it is a text field. I cut and pasted your code, and when I put my
curser
on the next line, an error message, "Compile Error: Expected: Line number
or
label or statement or end of statement. I wonder what I'm doing wrong.....

If VIN is a Text field (not a Number field in table design), you need
extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

Allen and Jerry: I tried both of your ideas, but I keep getting a
Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the
field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about using
both
the ODOStart field and the VIN field together. Was I supposed to put a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a SQL
feature. If it isn't, I couldn't find it.

:

Use the BeforeUpdate (or AfterUpdate) event procedure of the text box
on
your form to DLookup() the highest value so far for that vehicle, and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a table for vehicles and a table for trip details. Every day,
the
vehicle gets used, and the user must enter the odometer reading
before
the
route, and the odometer reading when the vehicle is brought back.
The
next
day, the process is repeated. A lot of times, the user puts the
wrong
odometer reading at the start of the trip. If the reading when the
vehicle
is
brought back the day before is 7000, and the user enters a number
less
than
7000 at the start of the next day's route, obviously it is a
mistake,
because
the number has to at least be what the ending number was the day
before.
If
this happens, is there a way I can have a message pop up to say that
"The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle ID,
and
TripID as the Route ID in 2 separate tables. Thanks for your help.

 

[Allen Browne]

Two possiblities:

a) Just set the Control Source propety to the expression.

b) Set the property to:
[Event Procedure]
Click the Build button (...) beside the property.
Access opens the code window.
Enter something like this:
[Text00] = DLookup(...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm sorry....I'm not sure what you mean...in your first post you said to
put
it in the beforeupdate or afterupdate property. Then where do I put the
message?

Sounds like you pasted in in the code window?

It was intended for the Control Source property of a text box.

If you want to put it in code, add the name of the control you want to
assign it to before the =.

Yes, it is a text field. I cut and pasted your code, and when I put my
curser
on the next line, an error message, "Compile Error: Expected: Line
number
or
label or statement or end of statement. I wonder what I'm doing
wrong.....

:

If VIN is a Text field (not a Number field in table design), you need
extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

Allen and Jerry: I tried both of your ideas, but I keep getting a
Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the
field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about
using
both
the ODOStart field and the VIN field together. Was I supposed to put
a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a
SQL
feature. If it isn't, I couldn't find it.

:

Use the BeforeUpdate (or AfterUpdate) event procedure of the text
box
on
your form to DLookup() the highest value so far for that vehicle,
and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a table for vehicles and a table for trip details. Every
day,
the
vehicle gets used, and the user must enter the odometer reading
before
the
route, and the odometer reading when the vehicle is brought back.
The
next
day, the process is repeated. A lot of times, the user puts the
wrong
odometer reading at the start of the trip. If the reading when
the
vehicle
is
brought back the day before is 7000, and the user enters a number
less
than
7000 at the start of the next day's route, obviously it is a
mistake,
because
the number has to at least be what the ending number was the day
before.
If
this happens, is there a way I can have a message pop up to say
that
"The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle
ID,
and
TripID as the Route ID in 2 separate tables. Thanks for your
help.

 

[Guest]

I finally got it to work! This is how I did it: When I tried Jerry's idea:

If Me.OdoStart < DMax("[OdoStop]", "Trips", "[VIN] = " &
Forms!Orders!VIN ) Then

it kept coming up with an error message that the code was incorrect. When I
tried Allen's last idea, there were no error messages, but no matter what
number I entered, the message box kept popping up. I then added Allen's code
to Jerry's DMax code, and it finally worked correctly. Here is what I used:

If Me.ODOStart < DMax("[OdoFinish]", "tblTrips", "VIN = """ & [VIN] &
"""") Then
MsgBox "The Odometer Number Entered is Incorrect"
End If

Thank you guys so much for your combined effort. Because of this forum, for
years I have been able to do the impossible and have learned so much.

Two possiblities:

a) Just set the Control Source propety to the expression.

b) Set the property to:
[Event Procedure]
Click the Build button (...) beside the property.
Access opens the code window.
Enter something like this:
[Text00] = DLookup(...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm sorry....I'm not sure what you mean...in your first post you said to
put
it in the beforeupdate or afterupdate property. Then where do I put the
message?

Sounds like you pasted in in the code window?

It was intended for the Control Source property of a text box.

If you want to put it in code, add the name of the control you want to
assign it to before the =.

Yes, it is a text field. I cut and pasted your code, and when I put my
curser
on the next line, an error message, "Compile Error: Expected: Line
number
or
label or statement or end of statement. I wonder what I'm doing
wrong.....

:

If VIN is a Text field (not a Number field in table design), you need
extra
quotes:
=DLookup("ODOStart", "tblTrips", "VIN = """ & [VIN] & """")

Allen and Jerry: I tried both of your ideas, but I keep getting a
Compile
Error: Syntax Error. I cut and pasted Jerry's and just changed the
field
names etc. to match mine, but it kept coming up with an error. With
Allen's,
this is what I put:

=DLookup("ODOStart", "tblTrips", "VIN = " & [VIN])
I then put the message underneath. I wasn't sure how to go about
using
both
the ODOStart field and the VIN field together. Was I supposed to put
a
greater than or less than sign as well?

I couldn't try Jamie's suggestion, because I think that CHECK is a
SQL
feature. If it isn't, I couldn't find it.

:

Use the BeforeUpdate (or AfterUpdate) event procedure of the text
box
on
your form to DLookup() the highest value so far for that vehicle,
and
give
the warning.

For help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

I have a table for vehicles and a table for trip details. Every
day,
the
vehicle gets used, and the user must enter the odometer reading
before
the
route, and the odometer reading when the vehicle is brought back.
The
next
day, the process is repeated. A lot of times, the user puts the
wrong
odometer reading at the start of the trip. If the reading when
the
vehicle
is
brought back the day before is 7000, and the user enters a number
less
than
7000 at the start of the next day's route, obviously it is a
mistake,
because
the number has to at least be what the ending number was the day
before.
If
this happens, is there a way I can have a message pop up to say
that
"The
Odometer Number Entered is Incorrect"? I have VIN as the vehicle
ID,
and
TripID as the Route ID in 2 separate tables. Thanks for your
help.