DLookup Multiple Expression required.

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Just for input validation purposes I have a field on a
form that is using a lookup to bring in an employees name
from a table "Contracts". I can get my lookup to work
when just bringing in the Surname or when bringing in the
FirstName but can not fathom how to bring in both the
FirstName and Surname to the same field. I have tried
using simple AND or & or &+ commands but just get an
error message each time. Any help please.
 
Hi Steve,

When you have code questions it is very helpful when you also post the code
that is causing problems since we don't have to guess about what you've
already tried. :-)

I'm not entirely clear on what you are actually trying to do but I think you
mean that you want to use more than one field in the criteria of the
dlookup. To use multiple criteria on a Dlookup you are merely forming a
clause which will ultimately look like this:

Surname = "Daigle" and FirstName = "Sandra"

Since you are building this by contcatenating the information from two
different controls it is a little trickier so it is helpful to use a
separate string variable for this clause alone, then use that variable as
the wherecondition parameter of the dlookup.

dim strCriteria as string
dim lngEmloyeeId as long
strCriteria="Surname=""" & me.Surname _
& """ AND Firstname = """ & me.FirstName & """"
lngEmployeeid=nz(dlookup("employeeid", "tblEmployees",strcriteria),0)

*The extra quotes are required since the final string must have embedded
quotes around the test values for the text fields.

*The Nz function is used to prevent a runtime error from occuring if dlookup
returns a null because there is no record matching the criteria.

Post back if this doesn't answer your question.
 
-----Original Message-----
Hi Steve,

When you have code questions it is very helpful when you also post the code
that is causing problems since we don't have to guess about what you've
already tried. :-)

I'm not entirely clear on what you are actually trying to do but I think you
mean that you want to use more than one field in the criteria of the
dlookup. To use multiple criteria on a Dlookup you are merely forming a
clause which will ultimately look like this:

Surname = "Daigle" and FirstName = "Sandra"

Since you are building this by contcatenating the information from two
different controls it is a little trickier so it is helpful to use a
separate string variable for this clause alone, then use that variable as
the wherecondition parameter of the dlookup.

dim strCriteria as string
dim lngEmloyeeId as long
strCriteria="Surname=""" & me.Surname _
& """ AND Firstname = """ & me.FirstName & """"
lngEmployeeid=nz(dlookup
("employeeid", "tblEmployees",strcriteria),0)
*The extra quotes are required since the final string must have embedded
quotes around the test values for the text fields.

*The Nz function is used to prevent a runtime error from occuring if dlookup
returns a null because there is no record matching the criteria.

Post back if this doesn't answer your question.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Just for input validation purposes I have a field on a
form that is using a lookup to bring in an employees name
from a table "Contracts". I can get my lookup to work
when just bringing in the Surname or when bringing in the
FirstName but can not fathom how to bring in both the
FirstName and Surname to the same field. I have tried
using simple AND or & or &+ commands but just get an
error message each time. Any help please.

.
Hi Sandra,

I am not trying to bring in a result based on two
criteria but to bring in two bits of data ie FirstName
and Surename based on only one criteria. I have attached
the code I have been trying but all I get returned is
Error.
=DLookUp("[FirstName]"
& " "+ "[Surname]","Contracts","[ContractNo]=" & [Forms]!
[Weekly Hours]![ContractNo])
Both Surname and FirstName work independantly but I am
trying to bring both into the same field and I can't find
any examples of this on the knowledge base or anywhere
else on the net.
Hope this explains further.
Grateful thanks for your help.
Steve.
 
*** snipped ***
.
Hi Sandra,

I am not trying to bring in a result based on two
criteria but to bring in two bits of data ie FirstName
and Surename based on only one criteria. I have attached
the code I have been trying but all I get returned is
Error.
=DLookUp("[FirstName]"
& " "+ "[Surname]","Contracts","[ContractNo]=" & [Forms]!
[Weekly Hours]![ContractNo])
Both Surname and FirstName work independantly but I am
trying to bring both into the same field and I can't find
any examples of this on the knowledge base or anywhere
else on the net.
Hope this explains further.
Grateful thanks for your help.
Steve.

Steve,
A DLookUp can only look up one Field at a time, not two.
You will need to have 2 separate DLookups.

NewField1 = DLookUp("[FirstName]" ,"Contracts","[ContractNo]=" &
[Forms]![Weekly Hours]![ContractNo])

NewField2 = DLookUp("[Surname]","Contracts","[ContractNo]=" & [Forms]!
[Weekly Hours]![ContractNo])

CombinedFields = [NewField1] & [NewField2]

Note: if the form that this is being done on is [Weekly Hours], and you
are doing this in VBA, then all you need is:
"[ContractNo]=" & Me![ContractNo])

If you were doing this in the control source of a text control, then:
"[ContractNo]=" & [ContractNo])
 
Back
Top