dlookup for Sales Rep Comp Rate

[Ada01]

Below is a rate table with ranges of sales required to get the stated Rate ("Comp_Rate_Table")

Agent Level
Range_Low
Range_High
Rate

Level One
0.00
32,000.00
        0.020

Level One
32,001.00
39,999.00
        0.025

Level One
40,000.00
9,999,999.00
        0.030

Level Two
0.00
50,000.00
        0.030

Level Two
50,001.00
74,999.00
        0.035

Level Two
75,000.00
9,999,999.00
        0.040

Partner/Trainer
0.00
50,000.00
        0.030

Partner/Trainer
50,001.00
74,999.00
        0.035

Partner/Trainer
75,000.00
9,999,999.00
        0.040

I have a crosstab query that brings together the various products and calcs to come to a final sales total ("Range AV") and summarizes the total by agent

Agent_Name
Level
Period Paid
Range AV
Prod A
Prod B
Prod C
6

John Doe
Level Two
201406
38,250.00
0.00
30,717.78
430.56
38,250.00

Jane Doe
Partner/Trainer
201406
64,375.00
0.00
23,741.04
2,274.84
64,375.00

John Smith
Level Two
201406
24,125.00
0.00
35,391.48
2,504.88
24,125.00

Jane Smith
Level One
201406
32,125.00
0.00
7,209.60
227.88
32,125.00

How do I get the Range AV and the Rate Table above to come together to give me the correct rate.

I believe a dlookup would be correct but the only part i know i am getting correct with that is the first two arguments:

 DLOOKUP([Rate],[Comp_Rate_Table], ????

writing the where statement to get the "Between function correct is not working in any form i try, would likely be because of a " or ' in the wrong spot.

Thanks

[June7]

Try:

DLookup("Rate", "Comp_Rate_Table", "[Agent Level]='" & [Level] & "' AND " & [Range AV] & " BETWEEN Range_Low AND Range_High")

However, I don't think can do this within CROSSTAB.

If there is possibility of rates changing over time, consider options:

1. saving record ID of rate record into data table, adding new records in Comp_Rate_Table for each rate change

2. saving the actual rate in effect into data table