Calculate a field value

sctb0825

Member
Local time
Yesterday, 20:25
Joined
Dec 28, 2021
Messages
53
I have a table with values, (see attached table snapshot) it is for calculating the current fuel surcharge example would be, if the current fuel price is between $4.85 and $4.90 the Fuel surcharge would be $0.75 per gallon.

I want to calculate the value based on the following Miles divided by 6, time the result from the above calculation = fuel surcharge.

So! it has to find the price range from the table, get the rate, Then divide the miles by 6 and multiple the result by the Rate. (see Form)

In my example it would look up the rate from the table which in this case is line 72 $4.85 FuelIndexLow to $4.90 FuelIndexHigh or $0.75 divide 999 by 6 = 166.5 Then multiple 166.5 x .75 = $124.87 an put that answer in the fuel Surchg filed on the form.

I can do simple calculations but in this case we are looking up a range. Can anyone help with this?
 

Attachments

  • SurchargeTable.jpg
    SurchargeTable.jpg
    80.9 KB · Views: 124
  • FuelIndex.jpg
    FuelIndex.jpg
    24.1 KB · Views: 127
I would create a dedicated function and call that.
Use DLookUp() to get your rate.
Pass in the price and miles, return your desired calculation.
 
I would create a dedicated function and call that.
Use DLookUp() to get your rate.
Pass in the price and miles, return your desired calculation.
Can you give an example with code?
 
Untested but try

Code:
Public Function GetSurcharge(FuelPrice as single) as Long
  dim criteria as string
  criteria = "FuelIndexLow <= " & fuelPrice & " AND FuelIndexHigh >= " & fuelPrice
  debug.print criteria
  getSurcharge = dlookup("fuelSurcharge","tblFuelSurcharge" ,criteria
end function

test this in the immediate window by passing in a value
?getSurcharge(4.87)
 
Untested but try

Code:
Public Function GetSurcharge(FuelPrice as single) as Long
  dim criteria as string
  criteria = "FuelIndexLow <= " & fuelPrice & " AND FuelIndexHigh >= " & fuelPrice
  debug.print criteria
  getSurcharge = dlookup("fuelSurcharge","tblFuelSurcharge" ,criteria
end function

test this in the immediate window by passing in a value
?getSurcharge(4.87)
Thank you will try
 
Here is an example of how to get a value from a range using pure sql. You cannot do a join but you add the range table and use a where criteria.
 
I would not use a function. I would use a join in the query. When working with a range, you need a non-equi-join. The QBE can only display joins using the = operator. So, you can use the QBE to create the join using a standard equi-join. Then switch to SQL view and change the operators on the join. Once you change the join operators, you will NOT be able to switch the query back to QBE view.

Select (SomeField/SomeOtherField) * tblSurcharge.TruckLoad As CalcSurcharge, .... other fields
From tblA Inner Join tblSurcharge ON SomeField >= tblSurcharge.FuelIndexLow and SomeField <= tblSurcharge.FuelIndexHigh;
 
I would not use a function. I would use a join in the query. When working with a range, you need a non-equi-join. The QBE can only display joins using the = operator. So, you can use the QBE to create the join using a standard equi-join. Then switch to SQL view and change the operators on the join. Once you change the join operators, you will NOT be able to switch the query back to QBE view
Example already provided. See thread #6
 
Example already provided
And I explained how to get around the limitation of the QBE which cannot display anything except equi-joins in QBE view.
 

Users who are viewing this thread

Back
Top Bottom