Dlookup with variable criteria

[JamesN]

Hi,

I'm trying to use a dlookup formula which references a combo box but unable to get it working.

I have a combo box (called workstate) which has different types of work, eg CaseID, IR, URN. In other cells I have a dlookup formula which I need to reference this combo box. I am currently using the below formula...

=DLookUp("Workstream","qadata","CaseID = '" & [caseid] & "'")

Both parts of the CaseID formula (column heading and combo box) need to reference the combo box "work state" as i have matching column headings in my table the data is pulling from and in the combo box drop down. The dlookup is needed to tell the formula which column needs to be looked up eg CaseID or IR or URN.

Thanks in advance

 

[JamesN]

Apologies just to add to this....the red highlighted part of the formula needs to reference the workstate combo box (not the case id bit in green, this needs to stay as caseid)

=DLookUp("Workstream","qadata","CaseID = '" & [caseid] & "'")

 

[CJ_London]

bit confused - are you saying your combo has a rowsource based on the qadata query?

perhaps explain what your data looks like because as the moment it sounds as if it is not normalised which makes any data extraction/manipulation tricky at best.

 

[JamesN]

Sorry for the poor explanation....

What i'm basically doing is returning lots of different values from my table and pulling this back into the forn eg workstream being one, based on the 'caseid' entered. The complicated bit is that the case id selected could be from 1 of 5 different columns depending upon the work state of that record. Within the form there is a combo box (work state) which includes all 5 options and the user selects one of these depending upon the particular case id, this combo box is used so that the combo box can refence it and lookip the appropriate column for that caseid.

eg user selects IR from the work state combo and this in turn updates the dlookup as below to lookup the correct column..

=DLookUp("Workstream","qadata","IR = '" & [caseid] & "'")

 

[CJ_London]

OK - So what you are saying is you are using the combo to choose which field should be used in the criteria. If so, then try

=DLookUp("Workstream","qadata",workstate & " = '" & [caseid] & "'")

 

[JamesN]

Worked great thanks