DLookUp Two Criteria " & ' etc Problems

[mikeTTI]

=DLookUp("Rate","tblLevyRates",[tblLevyRates]![LevyYear]=[LevyYear] And [tblLevyRates]![Species]=[Species])

The above is the control source for a textbox on a subform.

I just cannot get this to work. I have tried mimicing all the example I could find in the various threads, but no luck with any of them, I just get #Name? in the textbox.

I am too thick to understand where I need to include ' s and " s and & s, and I think this is the root of the problem.

Species is Text
Rate is Currency
LevyYear is Long Integer

This is related to a previous thread I have posted, and is one potential solution I am tinkering with.

Can anyone correct the way I have written the function?

 

[RuralGuy]

Try:
=DLookUp("Rate","tblLevyRates", "[LevyYear]= " & [LevyYear] & " And [Species]= '" & [Species] & "'")

 

[mikeTTI]

Thanks heaps Rural Guy. That works perfectly. I am in your debt.

I hope it is a nice day up there in the Rockies.

 

[RuralGuy]

Windy and snowy today. I love it! And you are welcome.

 

[mikeTTI]

Hi Rural guy,

Glad to hear you are enjoying the snow I have one more question.

Do you know of a way to stop the calculated field with that DLookUp in it displaying an error message in the row at the very bottom of the datasheet
(the one with * in the record selctor column.

This seems to be stopping a sum field in the form footer from working.

 

[RuralGuy]

Try:
=IIF(Len([Species] & "") = 0," ",DLookUp("Rate","tblLevyRates", "[LevyYear]= " & [LevyYear] & " And [Species]= '" & [Species] & "'"))