Validation incorrect

L

Loni - RWT

I have code in a field on a form to validate the entry to be not less than
the previous entry. It has been working fine but when I went from 99920 to
100004 the error message displayed. I set a break point and checked the
variables and they had the correct numbers. What is the reason for the error
message displaying?
Here is my code:
Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")

If Me.Odometer < varPrevOdometer Then
Cancel = True
Me.Odometer.SelStart = 0
Me.Odometer.SelLength = Len(Me.Odometer.Value)
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If

Thanks for your help.
 
B

Brendan Reynolds

Try "If Val(Me.Odometer) < Val(varPrevOdometer) Then"

It appears that your current code is doing a textual comparison rather than
a numeric one.

If the value may be Null, you'll need to check for that first ...

If IsNull(varPrevOdometer) Then
'whatever you want to do if it Null
Else
If Val(Me.Odometer) < Val(varPrevOdometer) Then
'etc
 
L

Loni - RWT

Thanks for your quick reply. That took care of that situation but another
arose. The calculation for the leg miles now is adding instead of just
calculating the current leg. I tried adding the Val() there but there was no
change.
Here is my code:
Private Sub Odometer_BeforeUpdate(Cancel As Integer)
Dim varPrevOdometer As Variant

varPrevOdometer = DLookup("MaxOfOdometer", "qryLastOdometer")

If Val(Me.Odometer) < Val(varPrevOdometer) Then
Cancel = True
Me.Odometer.SelStart = 0
Me.Odometer.SelLength = Len(Me.Odometer.Value)
MsgBox _
"The last odometer entered for this truck was " & _
varPrevOdometer & vbCrLf & _
"Please enter an odometer greater than or equal " & _
"to this.", , _
"Invalid Odometer Entry"
End If


Me.LegMiles = Val(Me.Odometer) - Val(varPrevOdometer)
Thanks
 
L

Loni - RWT

After my last post I noticed other problems. I looked at the table design
and the Odometer field was set to text. I changed it to number and reset my
code to the original and everything works correctly. I think the reason I
had it set to text was to eliminate the "0" in a blank cell. It's no big
deal but is there a way to eliminate this?
Thanks again for your help.
 
J

John Vinson

Loni - RWT said:
I think the reason I
had it set to text was to eliminate the "0" in a blank cell. It's no big
deal but is there a way to eliminate this?
Click to expand...

Simply open the Table in design view and remove the 0 in this field's
DefaultValue property. Select the field row and look at its properties in the
lower left of the screen.
 
Top