ms office - How do I substitute one entry for another an MS Access report? -

i started working ms access 2010, , trying generate labels form have created. in form, 3 pieces of information put in user: style, color code, , unit of measure (uom). style numbers appear same way on form , in report, , have been able work.

however, color code, need both inputted color code , actual color show on report. have table has of color codes corresponding color names. cannot figure out how text box supposed show color name show it. know virtually no sql, found information on on internet , pieced code in controlsource text box color name supposed in:

=(select [description] [color] where([forms]![box label form]![thirdjoined]=[color]![colorcode])) 

[description] name of column within [color] table gives actual color name.

[box label form] name of form.

[thirdjoined] name of input text box within form.

[colorcode] name of column within [color] table gives color code.

this code doesn't work, , results in #name appearing in print preview view. how can work, either code-wise or otherwise?

you cannot set controlsource of textbox sql statement. set dlookup function lookup single value. need separate out reference form control using concatenation (&).

=dlookup("description","[color]","colorcode='"&[forms]![box label form]![thirdjoined]&"'") 

i'm assuming color text-value form-value needs enclosed in apostrophes.

if expression used on form [box label form] don't need qualify name of control:

=dlookup("description","[color]","colorcode='"&[thirdjoined]&"'")