IIF Statement

[Guest]

I have a bunch of fields
Mileage Date1
Date of Change1
Mileage Cost1
Date of Change2
Mileage Cost2
Date of Change3
Mileage Cost3

If mileage date1 is on or between Date of Change1 and Date of Change2 then
Mileage Cost1

Then I need Date of Mileage date1 ---Date of Change2 and Date of Change3
then Mileage Cost2

I get it to work but it is not that accurate

 

[Guest]

Your should have the following fields --
VehicleID
Mileage
DateOfRec
MilesCost

Maybe also --
FuelQTY
LubeQTY
FuelCost
LubeCost
MiscCost
MiscRmks

Do not make a record horizontally but a new entry for every fillup, oil
change, or filter/belt/wiper blade replacement.

 

[Joseph Meehan]

I have a bunch of fields
Mileage Date1
Date of Change1
Mileage Cost1
Date of Change2
Mileage Cost2
Date of Change3
Mileage Cost3

If mileage date1 is on or between Date of Change1 and Date of Change2
then Mileage Cost1

Then I need Date of Mileage date1 ---Date of Change2 and Date of
Change3 then Mileage Cost2

I get it to work but it is not that accurate

Bad table design. You want to "Normalize" the data. That is you don't
want repeated entries of the same data in a record.

Think of a family.

Family Table:
Mother
Father
Last name
Address
Phone number
Child Table:
First name
Cell phone number

etc.

Once you do that, you will have progressed past the first serious
leaning curve of relational database. It is just the first step, but a very
important one. Not normalizing your data will create problem after problem
on down the line.