Using Module to Select Value

Gerrit.B

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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.
 

Gerrit.B

Board Regular
Joined
Aug 10, 2004
Messages
234
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
:eek: That's great until your weight breaks change, then you will wish you had listened to me

regards,
Giacomo
 

Forum statistics

Threads
1,136,352
Messages
5,675,269
Members
419,558
Latest member
Ryanms

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top