Unanswered: Lookup without Join; my latest problem
I am trying to determine a transportation fuel surcharge based on date range and transportation method. I have two tables, pertinent data therein listed below:
Table 1: [10000 records]
Transportation Method (either 'A' or 'B')
Ship Date (Gregorian)
Table 2: [15 records]
Start Date (Gregorian)
End Date (Gregorian)
Surcharge for method A
Surcharge for method B
I am trying to return the surcharge corresponding to (1) which Date Range the ship date corresponds with and (2) which transportation method is in the record in Table 1.
For simplicity's sake, I'm going to just return "ARt" for everything (I know how to make the chooser based on methods A and B.)
The function I created and put into the query is as follows:
Function Surcharge(ShpDt As String, begin As String, Finish As String, Method As String, ARt As Double, BRt As Double) As Double
Select Case PUdt
Case begin To Finish
Surcharge = ARt
Surcharge = 9999
The reason I set the "else" surcharge to 9999 is that sometimes the surcharge applied within a given date range is legitimately zero.
My problem is that I only want it to return the number of rows in Table 1. It's returning the product of the numbers of rows in Table 1 and Table 2. If there's a way to program this in, that would be preferable - setting criteria on the surcharge or grouping the result of the query takes a long time and doesn't sit well with me.
Does anyone have any suggestions?
I would greatly appreciate any help that could be provided!