# Using Module to Select Value

#### Gerrit.B

##### Board Regular
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

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.

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

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.

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:
Code:
``````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.

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])))

But i thought it was easier by using a module.

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

regards,
Giacomo

Replies
1
Views
230
Replies
2
Views
138
Replies
0
Views
454
Replies
3
Views
265
Replies
23
Views
5K

1,203,483
Messages
6,055,663
Members
444,806
Latest member
tofanexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back