Calculate Mileage Cost

[depawl]

I have a database that tracks, among other things, the cost of mileage travelling to and from service calls. In a query, there is a formula:
([# of Miles]*0.33)
So this multiplies the miles travelled by $0.33. This is an old rate (several years) and I need to update it. But I can't just change it to the new rate because all past records will be changed. What I need to do is to have all past records remain the same (at the $0.33 rate) and all new records use the new rate (for instance $0.56).
Any idea how to accomplish this?
Thanks in advance.

 

[RuralGuy]

This is actually a situation where historically, each record should have the rate. It needs its own field.

 

[depawl]

Each record does have a rate, it's the same for all of them. I have no need to change past records, they have all been billed and paid at the $0.33 rate. All I need to do is change the rate from this day forward to a new rate.
Thanks.

 

[RuralGuy]

What causes you to believe "all past records will change"?

 

[depawl]

Sorry, existing records won't actually change, but when I attempt to run a report of past records that uses this particular query as it's source (my main report does), the past records that I pull up will be inaccurate if I change the mileage rate in this query.

 

[RuralGuy]

Since each record has the milage rate, I'm confused as to why you would be changing the rate in the query. What is your query doing?

 

[Rabbie]

I have a database that tracks, among other things, the cost of mileage travelling to and from service calls. In a query, there is a formula:
([# of Miles]*0.33)
So this multiplies the miles travelled by $0.33. This is an old rate (several years) and I need to update it. But I can't just change it to the new rate because all past records will be changed. What I need to do is to have all past records remain the same (at the $0.33 rate) and all new records use the new rate (for instance $0.56).
Any idea how to accomplish this?
Thanks in advance.

If you use a function to calculate the mileage cost you can use the rate that was current for the date of the journey

 

[depawl]

ruralguy:
Each record has a field for # of miles travelled.
The query multiplies the # of miles * 0.33, to get the mileage charge.
What I need to do is change all future records entered to calculate an updated mileage charge (0.57 for instance) without affecting past records. If I change the 0.33 in the query to 0.57, I believe it will change the mileage in all past records.
Thanks again for your input.

 

[depawl]

Rabbie,
How would one do that (use a function)?
Can you give me an example?
Thanks so much.

 

[RuralGuy]

ruralguy:
Each record has a field for # of miles travelled.
The query multiplies the # of miles * 0.33, to get the mileage charge.
What I need to do is change all future records entered to calculate an updated mileage charge (0.57 for instance) without affecting past records. If I change the 0.33 in the query to 0.57, I believe it will change the mileage in all past records.
Thanks again for your input.

In post #3 you said "Each record does have a rate". If that is true then you should be using that field in the milage calculation and not some hard coded value in a query.

 

[depawl]

Sorry, on further review my post #3 was in error. Each record has a mileage cost, not a mileage rate. The rate is indeed hard coded in the query (Not my design).
Is it possible to create an expression in the query that does this?
Mileage Charge: (If [date] < 10/31/2008, then =[# of Miles]*0.33; otherwise = [# of Miles]*.56)

 

[RuralGuy]

It would need to be an immediate IF (IIF) but how about just creating your own function in a standard module and pass it the date and the miles. I suppose adding a new field to the table is out of the question? The rate should really be coming out of a table that can be altered. What do you do when it goes up again?

 

[depawl]

No, adding a field is not out of the question. However, I'm sure that my client does not want to have to enter the rate for every record.
I can create a new table that would have the rate, but I'm not sure how that would address the problem of the rate changing with time.
And your suggestion of "creating your own function in a standard module and pass it the date and the miles" unfortunately is beyond my level of expertise.
I was able to create and IIF statement that works fine, but your right, what happens when the rate changes again?

 

[RuralGuy]

You could set it up to automatically pick up the rate from the table. As I see it, it is only going to have one record. You need a form to change the value when needed is all.

 

[depawl]

ruralguy.
Thanks again for sticking with me on this. I kind of get the jist of what you are saying. I'll give it a try.
depawl

 

[WayneRyan]

depawl,

You can add a new table:

tblRates
========
RateID - AutoNumber
RateDate - Date
curRate - Currency

Contents might be:

1, 1/1/2000, $0.32 <-- earliest possible journey date
2, 3/2/2003, $0.40
3, 6/1/2008, $0.52

Then, a simple function call in a query:

WhichRate: GetRate([JourneyDate])

In a module, put the following:

Public Function GetRate(SomeDate As Date) As Currency
  GetRate = DLookUp("[CurRate]", "YourTable", "[RateDate] < #" & CStr(SomeDate) & "#")
End Function

Then you can have a rate for many different "windows" into your past data.

hth,
Wayne

 

[depawl]

Thanks Wayne.
this looks interesting but unfortunately is beyond my knowledge of access and vba.;
Appreciate the info though
depawl