DLookup in datasheet view

I have a form in datasheet view that I would like one field to be populated based on the value entered in another field. I accidently got this to work one day and for the life of me can't figure out how to do it again. For what it's worth, the dlookup needs to take the value the user enters, retrieve a corresponding (or masterID) field in one table, then use the MasterID to retrieve the data desired from yet another table.

Re: DLookup in datasheet view

OK. I got it working. However, there are different discounts assigned to the MasterID's and it wants to return the "B" discount no matter what. I need to throw in an IIF([DSDID]<1000,"S",IIF([DSDID]<100000,"D","B")) as the second part of the criteria, but I keep getting a syntax error. I think I'm putting my quotes in the wrong place.

'this part works in returning a field with the "B" discount:
=DLookUp("[ZipCIN].CrossRef","[ZipCIN]","[DSDID]='" & [Forms]![Orders]![DSDID] & '")

'it should be close to this:
=DLookUp("[ZipCIN].CrossRef","[ZipCIN]","[DSDID]='" & [Forms]![Orders]![DSDID] & '" & IIF([DSDID]<1000,"S",IIF([DSDID]<100000,"D","B")))

Can anyone see what I'm doing wrong?

Re: DLookup in datasheet view

Anybody?

You are allowed to throw in an IF statement in a DLookup() aren't you?

The formula below works but returns the first instance of the DSDID, which happens to be a "B" discount. I need it to DLookup(): IF(DSDID<1000, "S", IF(DSDID<100000, "D", "B")).

Can anyone help me, or know of a better way?

=DLookUp("[CrossRef]","ZipCIN","[DSDID] = '" & Forms![Temp Orders]!DSDID & "'")

Re: DLookup in datasheet view

I got it. I had my quotes in the wrong place for one, but mainly DSDID is a text field that could contain "94Z" (among others). I fixed it with IsNumeric().

Maybe this will help someone else.

=DLookUp("[CrossRef]","ZipCIN","[DSDID] = '" & Forms![Temp Orders]!DSDID & "'" & " AND Discount = '" & IIf(IsNumeric([DSDID]),IIf([DSDID]<1000,"S","D"),"B") & "'")