DLookUp and Concatenation

[sam8114]

I'm fairly new to access and trying to convert some of my formulas I use in excel to access.

I currently have a list of names with their pay grade. I am trying to take the pay grade and look up the actual amount of money they make during overtime.

I currently have a query concatenating the grade and step for each employee. What I now need to create is the dlookup to take that concatenation from the first query and go to look it up in another table and pull the dollar amount over.

However, the query with the dlookup is not recognizing the column with the concatenation. I tried to create the formula for the dlookup in the same query as the query I create the concatenation but it would not work. I also tried to set it in a query by its self and it did not work there either. Is it possible to have in the same query perform the concatenation and the dlookup look at that column and pull the information over? Is there a reason why it would not recognize the column with the concatenation formula?

This is my concatenation formula:

Please Login or Register  to view this content.

This is my dlookup formula... The OT Info is generated by the concatenation formula.

Please Login or Register  to view this content.

I get the error "Microsoft access cannot find the name 'OT Info' you entered in the expression'


Any assistance/advice is greatly appreciated.

[ranman256]

DO NOT use DLookUp in a query.
DLookUp is usu in a form to get 1 bit of info.

If you are using a query, JOIN to the table ("tbl_pay_scale") that has the info from the main table field "[OT INFO]"

(plus your example MUST use strings as the criteria: DLookUp("[OT RATE]","tbl_pay_scale","[OT INFO]='G/S'")
but not in a query.