Dlookup help needed

[gtslabs]

I am having problems getting a Dlookup to work in a query.

I have:
Expr1: DLookUp([Client],[tbl-ProjectNumbers],"[tbl-ProjectNumbers]!
[ProjectNumber] =" & [qry_ExcelSamples]![ProjectNumber])

I want to bring in the Client from my table tbl-ProjectNumbers. My
query already has the project number but since my relationships are
not set up for this I can not bring in my Client directly as a field.
So my I tried using the format for a combo box on a form but I am not
in a form. qry_excelSamples is my query name that I am working in and
I want to reference the projectnumber field but it is not working.
Any ideas on how to get this to work?

 

[John Spencer (MVP)]

DLookup requires STRINGS for all its arguments.
Expr1: DLookUp("Client","[tbl-ProjectNumbers]","[ProjectNumber] =" &
[qry_ExcelSamples]![ProjectNumber])

If ProjectNumber is a text field then you need to surround the value you are
looking for with quote marks. One method to do so is:
DLookUp("Client","[tbl-ProjectNumbers]","[ProjectNumber] =""" &
[qry_ExcelSamples]![ProjectNumber] & """")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I am having problems getting a Dlookup to work in a query.

I have:
Expr1: DLookUp([Client],[tbl-ProjectNumbers],"[tbl-ProjectNumbers]!
[ProjectNumber] =" & [qry_ExcelSamples]![ProjectNumber])

I want to bring in the Client from my table tbl-ProjectNumbers. My
query already has the project number but since my relationships are
not set up for this I can not bring in my Client directly as a field.
So my I tried using the format for a combo box on a form but I am not
in a form. qry_excelSamples is my query name that I am working in and
I want to reference the projectnumber field but it is not working.
Any ideas on how to get this to work?

All three arguments to DLookUp (and the other domain functions) must be text
strings - the first must be a text string naming the field, the second a text
string naming the table or query. Just enclose these in quotes. The third
argument needs to be a valid SQL WHERE clause without the word WHERE; you
don't need the table reference (since the domain for this DLookUp *IS*
tbl-ProjectNumbers), and you don't need the qry_ reference either (since this
dlookup is itself included within qry_ExcelSamples).

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] =" &
[ProjectNumber])

If ProjectNumber is a Text field you need the syntactically required
quotemarks around the criterion:

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] ='" &
[ProjectNumber] & "'")

Expanded for readability (don't DO it this way) that's

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] =' " &
[ProjectNumber] & " ' ")

 

[gtslabs]

I am having problems getting a Dlookup to work in a query.

I have:
Expr1: DLookUp([Client],[tbl-ProjectNumbers],"[tbl-ProjectNumbers]!
[ProjectNumber] =" & [qry_ExcelSamples]![ProjectNumber])

I want to bring in the Client from my table tbl-ProjectNumbers.  My
query already has the project number but since my relationships are
not set up for this I can not bring in my Client directly as a field.
So my I tried using the format for a combo box on a form but I am not
in a form.  qry_excelSamples is my query name that I am working in and
I want to reference the projectnumber field but it is not working.
Any ideas on how to get this to work?

All three arguments to DLookUp (and the other domain functions) must be text
strings - the first must be a text string naming the field, the second a text
string naming the table or query. Just enclose these in quotes. The third
argument needs to be a valid SQL WHERE clause without the word WHERE; you
don't need the table reference (since the domain for this DLookUp *IS*
tbl-ProjectNumbers), and you don't need the qry_ reference either (since this
dlookup is itself included within qry_ExcelSamples).

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] =" &
[ProjectNumber])

If ProjectNumber is a Text field you need the syntactically required
quotemarks around the criterion:

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] ='"&
[ProjectNumber] & "'")

Expanded for readability (don't DO it this way) that's

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] =' " &
[ProjectNumber] & " ' ")

I get the specified field [ProjectNumber] could refer to more than one
table in the FROM statement of the SQL...

 

[John W. Vinson]

I get the specified field [ProjectNumber] could refer to more than one
table in the FROM statement of the SQL...

Then you will need to qualify the ProjectNumber - the one outside the quotes -
with the tablename. You didn't post the SQL of your query so nobody here has
any way to tell you what that is, but it should be something like

Expr1: DLookUp("[Client]", "[tbl-ProjectNumbers]", "[ProjectNumber] =" &
[tablename].[ProjectNumber])

Note that the ! delimiter is for controls on a form, not for fields in a
table; use a period instead.