DLookup not working

[RJB]

I'm trying to use the DLookup function to return a value in a form from a table that is different from the table the form is created from.

Original Table is called: CT Armed Forces
Form is called: SAD - Create Order
The other table is called: Base Pay

Based on the individual's grade, I am trying to have it return the correct base pay for that grade.
Here is what I have:

=DLookUp("[Pay Grade]","[Base Pay]","[Pay Grade] = " & [Forms]![SAD - Create Order]![Grade])

"Pay Grade" is the Field Name in the "Base Pay" Table
"Grade" is the Field Name is the Field Name in the Form and the "CT Armed Forces" table.



What I get is the message"#Error" flashing repetitively.

[Minty]

If grade is a text field then you'll need to escape it with quotes , and you don't need the full form reference assuming Grade is a control on your form;

=DLookUp("[Pay Grade]","[Base Pay]","[Pay Grade] = '" & me.Grade &"'")

[ranman256]

is "[Pay Grade]" numeric. If so, yours is correct,
if it is string then you must have quotes,

=DLookUp("[Pay Grade]","[Base Pay]","[Pay Grade] = '" & [Forms]![SAD - Create Order]![Grade] & "'" )

[ssanfu]

Note: having spaces, punctuation or special characters (exception is the underscore) in object names is considered bad programming practice.


Examples
--------------------------

Bad Object Name	Better Object Names
CT Armed Forces	CTArmedForces or CT_ArmedForces or CT_Armed_Forces
SAD - Create Order	SADCreateOrder or SAD_CreateOrder or SAD_Create_Order
Base Pay	BasePay or Base_Pay
Pay Grade	PayGrade or Pay_Grade

[RJB]

This worked. Thank you