Dlookup between dates 1

[BradCollins]

I am hoping someone can show where I have gone wrong with the following dlookup statement that I have created.

it is to get a particular rate (curreny figure) based on a rate schedule ID that is on my form.
Each rates schedule can have multiple rates assigned to it, each rate being applicable for a certain date range.

I need to look up the applicable rate based on a chosen date so I have created the dlookup below

=DLookUp("fldRatesRate","tblRates","[txtRatesSchedule]=[fldRatesSchedID] and [fldBookIn] between #" & [fldRatesApplicableFrom] & "# and #" & [fldRatesApplicableTo] & "#")

Can anyone show me where I am going wrong please ?

Thanks

 

[PHV]

Perhaps this ?
=DLookUp("fldRatesRate","tblRates","[txtRatesSchedule]='" & [fldRatesSchedID] & "' and [fldBookIn] between #" & [fldRatesApplicableFrom] & "# and #" & [fldRatesApplicableTo] & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

That returns the same error that I already have being #Name?

would it matter that [txtRatesSchedule] is also a calculated field ?, ie: its value is :
=DLookUp("fldRatesSchedID","tblAccomodation","[cmbPickAccomodation]=[fldAccomID]")

 

[PHV]

And this ?
=DLookUp("fldRatesRate","tblRates","[fldRatesSchedID]='" & [txtRatesSchedule] & "' and [fldBookIn] between #" & [fldRatesApplicableFrom] & "# and #" & [fldRatesApplicableTo] & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

nope, same response (#Name?)

I tried
=DLookUp("fldRatesRate","tblRates","[txtRatesSchedule]=[fldRatesSchedID]) and it works fine returning the first value of course, it just errors when I add the date sections

 

[PHV]

What are the fields in tblRates vs the controls in the form ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

The form is based on tblBookings, I have drop down list so the user can select a room to book (cmbPickAccomodation) this shows a list of rooms frm tblAccomodation

then I have an unbound field (txtRatesSchedule) with a control source of
=DLookUp("fldRatesSchedID","tblAccomodation","[cmbPickAccomodation]=[fldAccomID]")

this will return the appropriate rates table that is allocated to the chosen room.

On the form there are two date fields (fldBookIn & fldBookOut)

I have now created another unbound field (txtRate) with the control source
=DLookUp("fldRatesRate","tblRates","[fldRatesSchedID]='" & [txtRatesSchedule] & "' and [fldBookIn] between #" & [fldRatesApplicableFrom] & "# and #" & [fldRatesApplicableTo] & "#")

of course it is this that is not working.

the rates table that I am trying to get the rate from consists of the following

tblRates
fldRatesID (PK)
fldRatesSchedID (FK)
fldRatesDescription (text)
fldRatesApplicableFrom (date/time)
fldRatesApplicableTo (date/time)
fldRatesRate (currency)

I have another table tblRatesSchedule that contains
fldRatesSchedID (PK)
fldRatesSchedDesc (text)

The following relationships exist:
tblRatesSchedules.fldRatesSchedID (1 to Many) tblAccomodation.fldRatesSchedID

tblRatesSchedules.fldRatesSchedID (1 to Many) tblRates.fldRatesSchedID

Thank you again for all your help it is most appreciated.

 

[PHV]

And this ?
=DLookUp("fldRatesRate","tblRates","[fldRatesSchedID]='" & [txtRatesSchedule] & "' and #" & [fldBookIn] & "# between [fldRatesApplicableFrom] and [fldRatesApplicableTo]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

that one returns #Error

Maybe with this I am just getting to adventurous, its has been troubling me for nearly a week now.

 

[PHV]

If fldRatesSchedID is defined as numeric in tblRates then:
=DLookUp("fldRatesRate","tblRates","[fldRatesSchedID]=" & [txtRatesSchedule] & " and #" & [fldBookIn] & "# between [fldRatesApplicableFrom] and [fldRatesApplicableTo]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

Almost, I am now getting the rate returned, but it is still showing the wrong rate

in my rates table (test date) I have 2 rates for it to choose from

fldRatesID (PK) 1 2
fldRatesSchedID (FK) 1 1
fldRatesDescription (text) Summer Winter
fldRatesApplicableFrom (date/time) 1/10/08 1/4/08
fldRatesApplicableTo (date/time) 31/3/09 30/9/08
fldRatesRate (currency) $100 $80

each time I enter a test booking, it shows the rate as $100 if fldBookIn = 14/10/08 then rate shows as $100 (Correct)
fldBookIn = 11/7/08 then rate = $100 (should be $80)

 

[PHV]

=DLookUp("fldRatesRate","tblRates","[fldRatesSchedID]=" & [txtRatesSchedule] & " and #" & Format([fldBookIn],"yyyy-mm-dd") & "# between [fldRatesApplicableFrom] and [fldRatesApplicableTo]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

PHV

What a champion effort !!!!

Thank you very much, that has got it. All works like it should now.