Using Module to Select Value

[Gerrit.B]

I use function below, now I want to change this to select correct columm in table for calculation purposes.

Is this possible.

******************************************

Public Function RateSelector(myCheageableWeight As Double) As Double
Select Case myCheageableWeight

Case 0 To 3:
RateSelector = [tbl_GAP_Rates]![GAP1]
Case 3.00001 To 5:
RateSelector = [tbl_GAP_Rates]![GAP2]
Case 5.00001 To 10:
RateSelector = [tbl_GAP_Rates]![GAP3]
End Select

End Function

 

[SydneyGeek]

I'm a bit confused about what you need.

Do you always want to look up a certain field like you have here, and then return the value in a query?

If so, you need to create an expression in a query like this --
SelectedRate:RateSelector([TheFieldNameWithTheWeight])

Also, what will you do for values >10, or will you never have values that high?

Denis

 

[Gerrit.B]

I have a table with al values where i got rates for several weights like 0-3 Kg, 3-5 Kg and 5-10 Kg.
In the query i multiply the weight with the appropriate field given by the module.

 

[SydneyGeek]

OK,

I still am unclear about what you need. Do you already have it working in one query, adn you want to make it work somewhere else? Or have you built the function but you can't set up the appropriate expression to get what you want? Or is it something else?

Denis

 

[Gerrit.B]

I copied this function from another database where it gave me a value.
Now I want to give me the correct row for my formula.

Example:

if weight is 1 kg [tbl_B]![weight]*[tbl_A]![Rate1]
if weight is 5 kg [tbl_B]![weight]*[tbl_A]![Rate2]

Hope this will explain my question.

 

[giacomo]

tbl_GAP_Rates should be structured with one rate per row and a low rate and high rate. Then you can get the correct rate in a query like this:

SELECT tblB.weight * tbl_Gap_Rates.Rate as x
FROM tblB
, tbl_Gap_Rates
WHERE
tblB.Weight between tbl_Gap_Rates.Low_Weight and tbl_Gap_Rates.High_Rate;

hth,
Giacomo

EDIT: I should also add that low and high should not overlap. instead of 0 to 3, 3 to 5, and 5 to 10 use 0 to 3, 4 to 5, and 6 to 10. This will avoid any cartesian product.

 

[Gerrit.B]

I finaly solved this by using the IFF function below.


Expr1: IIf([CheargeableWeight]<10,[tbl_GAP_Rates]![GAP1],IIf([CheargeableWeight]<50,[tbl_GAP_Rates]![GAP2],IIf([CheargeableWeight]<250,[tbl_GAP_Rates]![GAP3],[tbl_GAP_Rates]![GAP4])))

Thankx for your time.

But i thought it was easier by using a module.

 

[giacomo]

That's great until your weight breaks change, then you will wish you had listened to me

regards,
Giacomo