Hello again,

I am having DLookup problems from a report. I want to use the DLookup function becase I have 15 fields which are reocccuring types (same data souce for the 15 but in another table). I store a numeric code in the main table and want to get the corresponding text from a lookup table. Should be easy.

Look up Table: tbl_Issues_Addressed
It has two fields: Issue_key defined as Autonum
Issue_addressed defined as text (this is what I want to retrieve)


In report “Case_Subreport_Appeals_New” (it is being tested as a report but will be deployed as a subreport, hence the name) I have a bunch of fields (15) from the main table which are defined a ‘numeric’ in their table, which I want to go to the Lookup table and retreive the text field: ‘Issues_Addressed’. I am now just working with one of them to find the correct expression, and then I will apply that format to the others.

This is the expression I put in the control source of the field on the report called ‘Appeals_Filing3_Issue2’:

=DLookUp("[Issue_addressed]","tbl_Issues_Addressed","[Issue_key]= [Reports]![Case_Subreport_Appeals_New]![Appeals_Filing3_Issue2]")

When I hard code the number '5' as seen below, (instead of of using a field from the report), it works:

=DLookUp("[Issue_addressed]","tbl_Issues_Addressed","[Issue_key]=5")

So it has something to do with the input from the report going into the function. Is the fact that the value is in a 'text' box making it fail? Do I need to convert it to a numeric type? That doesn't seem right. On the report I have obtained the field I want to use as a lookup value, from a table where it is defined as numeric.

Any suggestions, please?


Clinton Black

Try changing your syntax to:

=DLookUp("[Issue_addressed]","tbl_Issues_Addressed","[Issue_key]=" & [Reports]![Case_Subreport_Appeals_New]![Appeals_Filing3_Issue2])

Hope that helps...

Your DLookup is in the control named [appeals_filing3_issue2], and the DLookup references [appeals_filing3_issue2]. This looks like a circular reference to me. your criteria section of DLookup should be somthing like..., "[issuekey] =" & [reports]![case_subreport_appeals_new]![issuekey])

Thanks to you BOTH, I tried both suggestions the '&' does not seem to be interpreted well, with that in the equation the entire application abruptly ends. Thanks for the suggestions and have a good weekend!! Any other ideas always welcome.

Sorry, I don't see how/why it's not working. Maybe you should try doing a Compact and Repair on your db.