Access Dlookup Not Working

Question

I have a Dlookup formula in an Access query that I am using to return the rate to be used based on the payment date in the same query.

I have a separate query containing interest rates over different date ranges per the below.

For some reason the formula works but doesn't return the correct Interest Rates, it is just returning random incorrect rates from the interest rate query.

The formula I am using is

Interest Rate: DLookUp("[Rate]","Interest Rates Query","#" & Format([Payment Date],"dd-mm-yyyy") & "# between [Interest Rates Query]![Date From] And [Interest Rates Query]![Date To]")

I would greatly appreciate your help!

Thanks

Gustav Gustav · Accepted Answer · 2017-04-19T07:45:25

If it's the same query, try this:

DLookUp("[Rate]","[Interest Rates Query]","[Payment Date] Between [Date From] And [Date To]")

Edit:

If two queries, use:

Select 
    q1.*, q2.Rate
From
    q1, q2
Where
    q1.PaymentDate Between q2.DateFrom And q2.DateTo

Access Dlookup Not Working

1 Answers