DLookUp in a textbox

[fat controller]

I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.

I have a table (ComplaintsResponses) that has two fields, both text

[ShortDescription]
[ResponseText]

The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.

I have tried the following code:

=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [Reports]![PublicComplaintsArea]![txtSAPCRMResponse] & "'")

andn

=DLookUp("[ResponseText]","[ComplaintsResponses]","[ShortDescription='" & [SAPCRMResponse] & "'")

Both of which return a #Error in the text box.

The field that contains the text that is used for the lookup is SAPCRMResponse, and the textbox on the report itself is called txtSAPCRMResponse.

 

[Minty]

Your are nearly there - you haven't put the closing square bracket around the ShortDescription] .

You may also need to use Me.txtSAPCRMResponse to get it to pick up the detail correctly.

 

[fat controller]

Ach, I am a silly numpty - I tried that over and over again and didn't spot that missing square bracket. Thank you

PS - the me. was not needed, and in fact it didn't work with it in place