DLookUp on Report

[sanders720]

I have a table called tblCodeMap. It has two fields, Code and Code_a. Code_a is the abbreviation of the code. I am trying to do a DLookUp on this table based on the code field on the report detail in a textbox called Code_A. Unfortunately, I am getting "#Name?" in all the Code_A text boxes, thus the translation is not occuring. Below is my DLookUp statement which resides in the control source for the Code_A textbox.

=DLookUp("[Code_a]","tblCodeMap","

 = ' " & [Reports]![rptPurchasedPartsList]![Code] & " ' ")

Any idea's?  Thanks in advance for any help you can provide.

 

[CosmoKramer]

First, I would change the name of the text box on your report from Code_A to something else. Variable names in Access are not case sensitive, so its confused with your two code_a's.

The syntax of your DLookup statement is incorrect. If for example you changed your text box on the report to be txtRptCodeA, the DLookup might look like this:

=DLookUp("[Code_a]","tblCodeMap","[Code_a]=[txtRptCodeA]")

I hope this helps.....

 

[sanders720]

=DLookUp("[Code_a]","tblCodeMap","

 = ' " & [Reports]![rptPurchasedPartsList]![Code] & " ' ")

Problem not solved...

The first string I pasted looks like something was missing.  I want to obtain the abbreviated code for the non abbreviated code that already exists on my report.  Thus, lookup code_a in tblCodeMap where code=the contents of textbox code.  I have not renamed the textbox because it is used in datasheet view as well as on a form, and I want the column heading to be CODE.

Thanks again...

 

[CosmoKramer]

Well, good luck....But, until you change the field name and fix your DLookup statement, you'll continue to have problems.......

 

[JohnLowell]

You need to specify the column in the table you are comparing the Purchased Parts List to. Put the column name in place of the ???.

=DLookUp("[Code_a]","tblCodeMap"," ??? = '" & Me.rptPurchasedPartsList & "'&quot