Suggestions Please?!?

[Guest]

I have a table that contains the following fields:
Year, Month, Odometer Reading
On exit of the odometer reading field, I want to calculate mileage based on
the current odometer reading being entered and the previous months odometer
record and insert it into a mileage field. For example:
On my mileage form the user has entered 11(month), 2004(year) and
45632(odometer reading). I need to find the odometer reading in the mileage
table for 10(month), 2004(year). But I am not sure how to code it. I have
the following code already (which gives me everything but the past months
odometer reading):
Dim lngCurrentOdoReading As Long
Dim lngLastMonthOdoReading As Long
Dim lngCalcMileage As Long
Dim lngCurrentYear As Long
Dim lngCurrentMonth As Long
Dim lngCalcYear As Long
Dim lngCalcMonth As Long
lngCurrentOdoReading = milOdometerReading.Value
lngCurrentYear = milYear.Value
lngCurrentMonth = milMonth.Value
If lngCurrentMonth = 1 Then
lngCalcYear = lngCurrentYear - 1
Else
lngCalcYear = lngCurrentYear
End If
If lngCurrentMonth = 1 Then
lngCalcMonth = 12
Else
lngCalcMonth = lngCurrentMonth - 1
End If

Can anyone help me out, please? Thank you.

 

[Ken Snell [MVP]]

You might be able to use the DLookup function to retrieve the data from the
table for the previous month; you then can use those values in your
calculations. Take a look at it in Help and post back if you have additional
questions.