Mileage by date help

[Skylinefranc]

We are trying to find a way to set how much to pay for mileage based on date.

Example: anything before May 1st 2010 is 46 cents per mile and anything after that date is 50 cents a mile.

I'm thinking that we can use an If function to determine if the date is before or after and then based on which it is pay a certain amount for the mileage.

Before we would have to create two seperate mileage reports and manually go through and assign them by date and its honestly a big pain, any help would be greatly appreciated.

[comment]

This is best done by a lookup, not a calculation. Create a table of Costs with two fields: FromDate and Cost. Define a relationship between Costs and your current table, matching on date. Set the Cost field in your current table to lookup the value from Costs table, copying next lower value if no match.

[Skylinefranc]

Thanks for the info, rellay appreciate it.

[Skylinefranc]

Tried doing this with no luck, would you mind providing a quick example?

[comment]

See attached.

CostLU.zip

[Skylinefranc]

Thanks, the example helped a ton, I was able to get into it and see exactly how it is done for future uses as well. Greatly appreciate this.