DLookUp using Date() between two dates

[atrium]

I have a form that requires the user to enter the type of Interest they want for this schedule. In a drop down either Nominated (which they just enter the figure) or Court (where I'm trying to retrieve the appropriate rate from a table of rates that have a startdate and a stopdate.

I use the following

   If IntRateTypeFld = "Court" Then
     Me.TodaysDateFld = Date
     Me.InterestRateFld = DLookup("IntRate", "CourtIntRates", "[TodaysDateFld] Between #" & [StartDate] & "# AND #" & [StopDate] & "#")
     Me.InterestRateFld.Enabled = False
  Else
     Me.InterestRateFld.Enabled = True
  End If

I keep getting a run time error 2465
" Microsoft Access can't find the field '|1' referred to in your expression

I have checked the syntax

I have used the intermediate window and ? TodaysDateFld and it has todays date in it eg. 17/12/2015

The other fields are all spelt correctly

I'm at a loss to find the problem. Any help would be appreciated

 

[MarkK]

Try set a breakpoint and evaluate this in the immediate pane . . .

"[TodaysDateFld] Between #" & [StartDate] & "# AND #" & [StopDate] & "#"

What is the value, for instance, of "[StartDate]"? Where does that value come from?

 

[atrium]

The StartDate and StopDate come from the table CourtIntRates.

Do I need to qualify them with [CourtIntRates]![StartDate] ?

If Do what you suggested I get the same result

 

[arnelgp]

yes, if it is from a form:

[Forms]![CourtIntRates]![StartDate]

 

[atrium]

The [StartDate] and [StopDate] are from the table I'm doing the DLookUp on.

I'll try it anyway

Thanks

 

[atrium]

Same result Run time error etc

 

[arnelgp]

where does [TodaysDateField] comes from? from CourtIntRates table also:

DLookup("IntRate", "CourtIntRates", "[TodaysDateFld] Between [StartDate] AND [StopDate])

if it is in the form:

DLookup("IntRate", "CourtIntRates","#" & Format(Forms!yourForm![TodaysDateFld],"mm\/dd\/yyyy") & "# Between [StartDate] AND [StopDate]")

 

[atrium]

Thanks guys for your help

arnelgp you are the man. Works exactly as required

Thank you once again

Atrium