DLookup on multiple fields (1 Viewer)

[Robear Dyer]

I have 2 tables with multiple-field primary keys; each record is uniquely identified by both a FarmerID field and a Crop field.

On the form for Table2, I'd like a combo box to show what fertilizer was entered for that record in Table1. I've tried the following code:

=DLookup("[Fertilizer]","Table1","[FarmerID]AND[Crop] = " & [Forms]![Table2Form]![FarmerID]AND[Crop])

But the box only shows the fertilizer for the first record with that FarmerID in Table1, even when the crop is different.

Is there a way my form can show the correct fertilizer for each farmer/crop in Table1?

TIA

 

[Jack Cowley]

Try:

=DLookup("[Fertilizer]","Table1","[FarmerID] = " & [Forms]![Table2Form]![FarmerID] & " And [Crop] = '" & [Forms]![Table2Form]![Crop] & "'")

Note: the quotes after [Crop] are Single Quote then a Double Quote. And after the last ampersand it is Double Quote Single Quote Double Quote.

I am guessing that FarmerID is numerical and Crop is Text and that there is a field call Crop in your form.

[This message has been edited by Jack Cowley (edited 06-20-2001).]