Validation incorrect

[Guest]

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.

 

[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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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?

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.

 

[Guest]

Thanks,John. That works and let me clean up some other places as well.