Calculate a field value (1 Viewer)

[sctb0825]

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?

 

[Gasman]

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.

 

[sctb0825]

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?

 

[MajP]

Untested but try

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)

 

[sctb0825]

Untested but try

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

 

[MajP]

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.

Summing Continuous Form and Multiple Forms

Question1: I have a form with a couple of subforms. The main form is per project and has a ProjectID primary key. Both subforms are continuous forms and linked via the ProjectID to the main/parent form. One of fields in the subforms I would like to sum and store it but it has to be group by the...

www.access-programmers.co.uk

 

[Pat Hartman]

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;

 

[MajP]

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

 

[Pat Hartman]

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.