use another field with a between criteria on another field

[sspreyer]

hi ,

all

right I'm hoping someone can point me in the right direction

i have db that tracks vehicle mileage and servicing

i have created query to show which vehicles are due service by the mileage

my issue is some manufacturers set different service intervals

e.g
car manufacture A = service intervals every 10,000
car manufacture B = service intervals every 20,000

but i would like to give pre warning so I'm using between criteria

here is what i have much appreciate if there is a better why of doing this

im using Dmax to find the last mileage entered for the vehicle

findLast: DMax("[vehicle mileage]","[vehserviceTbl]","([vehicleidimp]= " & [vehicleid] & ")")

now the criteria

Ifs([make] = "A" , Between 9000 and 10000
ifs ([Make] = "B" , between 19000 and 20000

can't get the red text to work shows blank when query runs

cheers

shane

 

[pbaldy]

If you have a table with makes and intervals, you can join that table in this query which would let you refer to the interval field

Between IntervalsTable.Inverval - 1000 And IntervalsTable.Inverval