Dlookup Query syntax issue?

[bibbyd01]

Hi All

I have two questions really. firstly, if you have a dlookup in a query, are you still able to populate a form using the query (it's based on only 1 table, and the query will be used to populate a form which opens using criteria from another form). My assumption is that you can, so....

Secondly, I have a query I want the dlookup function to be used on. It's basically looking up an order number, looks at a different query, and then provides a name of the person responsible. The query is called qry_bill test. The field on the query I'm using is called tblWIPcomments_ord.

The query it needs to lookup the data on is called qry_allWIP, the field is tblZCSCOST_ord and the data I want back is called tblSuper_Name.

My expression is therefore

=DLookUp("[tblSuper_Name]","qry_allWIP","[tblZCSCOST_ord] = Query![tblWIPcomments_ord]")

I get an error saying that it can't find tblWIPcomments_ord but it's a field that is being displayed on the query. I'm guessing I'm doing something wrong but can't figure out what!

 

[AccessMSSQL]

Are you naming your fields like tables? It appears you are not referring to your actual field names in the dlookup function. tblZCSCost_ord seems like a table name to me.

 

[bibbyd01]

Hi

tblZCSCost is the table, tblZCSCost_ord is the field within the table (so I know which table the order field comes from).

 

[AccessMSSQL]

Your dlookup is not parsing out the WHERE part of the argument. So I think your syntax needs to be as below:

If tblWIPcomments_ord is a numeric field:

=DLookUp("[tblSuper_Name]","qry_allWIP","[tblZCSCOST_ord] = " & [tblWIPcomments_ord])

and if it's a text field:

=DLookUp("[tblSuper_Name]","qry_allWIP","[tblZCSCOST_ord] = '" & [tblWIPcomments_ord] & "'")

 

[bibbyd01]

That's brilliant. I didn't realise I had to do that in the query.