Dlookup

  • Thread starter Thread starter jbiggs via AccessMonster.com
  • Start date Start date
J

jbiggs via AccessMonster.com

Can't seem to get this dlookup correct:

DLookup("[ID]", "Clients", "[Salesperson] ='" & RepNum & " AND [task note] =
'Unworked Lead'")

The Salesperson part is not working although when I print it to screen it
looks correct. Salesperson is a text field, RepNum is a string.
 
If RepNum is a Number field, try:
=DLookup("[ID]", "Clients", "([Salesperson] = " & Nz([RepNum],0) & ")
AND ([task note] = 'Unworked Lead')")

If it is a Text field, try:
=DLookup("[ID]", "Clients", "([Salesperson] = """ & RepNum & """) AND
([task note] = 'Unworked Lead')")

For an explanation of the quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Nz() in the first example is to avoid the error when the number field is
null, and so the critiera would be mal-formed.
 
The Salesperson doesn't have a closing single quote

Try:

DLookup("[ID]", "Clients", "[Salesperson] ='" & RepNum & "' AND [task note] =
'Unworked Lead'")

If the Salesperson field is numeric remove the single quote
DLookup("[ID]", "Clients", "[Salesperson] =" & RepNum & " AND [task note] =
'Unworked Lead'")
 
Allen,

The 2nd example worked perfectly, thank you so much! I will check out your
link.

Allen said:
If RepNum is a Number field, try:
=DLookup("[ID]", "Clients", "([Salesperson] = " & Nz([RepNum],0) & ")
AND ([task note] = 'Unworked Lead')")

If it is a Text field, try:
=DLookup("[ID]", "Clients", "([Salesperson] = """ & RepNum & """) AND
([task note] = 'Unworked Lead')")

For an explanation of the quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Nz() in the first example is to avoid the error when the number field is
null, and so the critiera would be mal-formed.
Can't seem to get this dlookup correct:
Click to expand...
[quoted text clipped - 4 lines]
The Salesperson part is not working although when I print it to screen it
looks correct. Salesperson is a text field, RepNum is a string.
Click to expand...
Click to expand...
 
Back
Top