DLOOKUP returns #Error regardless of what I do. ARGH! Help!

[dab1477]

Need help, please. I'm frustrated. I have a form where I want to display a value from a table, based upon the selection from a combo box in the same form.

For instance. If Combo Box 1 is named CELL NAME. Display box on form is named GOAL-UPH. If my combo box named CELL name = SPECIALTY RF 1, I want DISPLAY Box named GOAL-UPH to return the number 88. The number 88 come from a table holding data for a variety of Cell Names.

The lookup table looks like this...
ID Dept. CellName Winter Staff Summer Staff Goal - UPH Goal-U/LH Goal - MPU
1 WS Velux/YKK 3.00 3.00 90 30 2
2 WS PlyGem DH 3.00 3.00 90 30 2
3 WS Specialty RF 1 3.00 3.00 88 29.3 2.05
4 WS Specialty RF 2 4.00 4.00 100 25 2.4
5 WS Clatyton/PlyGem 5.00 5.00 120 24 2.5
6 WS Rivet Cell 1 7.00 7.00 390 60 1
7 WS Rivet Cell 2 7.00 7.00 129 18.4 3.26
8 WS Ply Gem SH 2 5.00 5.00 360 72 0.83

All the numbers above are formatted in the table as General Number. All text is Short Text format.

My DLOOKUP for the GOAL-UPH box on the form (I have this in Control Source of properties) wanting to return the value based upon Cell Name on form, looks like this...

=DLookUp("Goal-UPH","tbl_celldataTN","cellname=" & [Cell Name])
The properties box for this GOAL-UPH box is set to Text Format = Plain TEXT.

I've also tried =DLookUp("[Goal-UPH]","[tbl_celldataTN]","[cellname]=" & [Cell Name]) without success.

This should work, but it returns #Error . I've researched this forum and multiple internet sites and I am not seeing my flaw. Can someone help me get pointed in the right direction? Thank you.

 

[bobsan42]

if the info you posted is correct the first thing that shows is that the field name GOAL-UPH actually contains two extra spaces around the dash so it actually is "[Goal - UPH]".

 

[stumac]

If cellname is a text value then you need to pass it as a text value:

DLookUp("[Goal-UPH]","[tbl_celldataTN]","[cellname]= [COLOR=#ff0000]'[/COLOR]" & [Cell Name][COLOR=#ff0000]&"'"[/COLOR])