Dlookup doesnt work; complete Details included

[tom]

The following control works fine

=[Forms]![form1]![invoice number]

The following control does not work nor any of its permutations that I can
figure

=DLookUp("[supplier name]","00query1","[other number] =" &
[Forms]![form1]![invoice number])


The following control will work.

=DLookUp("[supplier name]","00query1","[other number] ='6691'")

but of course that is not at all what I want.

 

[Roger Carlson]

If your "other number" is text then the DLookUp should look like this:

=DLookUp("[supplier name]","00query1","[other number] ='" &
[Forms]![form1]![invoice number] & "'")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

Your working hardcoded example has quotes around the value: you need those
in the non-hardcoded example as well.

=DLookUp("[supplier name]","00query1","[other number] ='" &
[Forms]![form1]![invoice number] & "'")

Exagerated for clarity, that 3rd parameter is

"[other number] = ' " & [Forms]![form1]![invoice number] & " ' "

 

[John Vinson]

The following control works fine

=[Forms]![form1]![invoice number]

The following control does not work nor any of its permutations that I can
figure

=DLookUp("[supplier name]","00query1","[other number] =" &
[Forms]![form1]![invoice number])


The following control will work.

=DLookUp("[supplier name]","00query1","[other number] ='6691'")

but of course that is not at all what I want.

Evidently [other number] is of Text datatype; therefore you need the
quotemark delimiters. Try

=DLookUp("[supplier name]","00query1","[other number] ='" &
[Forms]![form1]![invoice number] & "'")

John W. Vinson[MVP]

 

[Tom]

Thank you, I am confused by the &, What is the
rule for these?

Your working hardcoded example has quotes around the value: you need those
in the non-hardcoded example as well.

=DLookUp("[supplier name]","00query1","[other number] ='" &
[Forms]![form1]![invoice number] & "'")

Exagerated for clarity, that 3rd parameter is

"[other number] = ' " & [Forms]![form1]![invoice number] & " ' "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The following control works fine

=[Forms]![form1]![invoice number]

The following control does not work nor any of its permutations that I can
figure

=DLookUp("[supplier name]","00query1","[other number] =" &
[Forms]![form1]![invoice number])


The following control will work.

=DLookUp("[supplier name]","00query1","[other number] ='6691'")

but of course that is not at all what I want.

 

[Van T. Dinh]

& is a String concatenator operator combining 2 String operands. For
example, the expression:

"This " & "is " & "the " & "whole " & "sentence."

will result in the String.

"This is the whole sentence."

(without the double quotes).

 

[tom]

Thank you, though I still am not clear on why
they are needed in the dlookup criteria.

 

[Rick Brandt]

Thank you, though I still am not clear on why
they are needed in the dlookup criteria.

Because you (usually) do not want the form control references inside the
quotes.

 

[Van T. Dinh]

Basically, the criteria (3rd) argument of the DLookUp() function is a String
(that specifies your selection criteria. Thus, what you need in the end
is:, for example:

"[other number] = '6691'"

(without the double quotes, assuming that [Forms]![form1]![invoice number]
has the entry "6691". The single quotes above indicates that it a String
value, the same data type as the Field [other number]).

To get the above, i.e. including the single-quotes in your criteria argument
using the String concatenation, you need to use:

"[other number] ='" & [Forms]![form1]![invoice number] & "'"

when Access evaluates the above expression, it will replace the reference
[Forms]![form1]![invoice number] with the String value "6691" (without the
double-quotes) and the single quotes are considered as literals to be
included in the result (because the single quotes are inside the String
delimiter "). The double-quotes are all "consumed" by the concatenation
process and thus, the result of the concatenation is:

"[other number] = '6691'"

(without the double quotes) and it is the correct input for the criteria
argument of the DLookUp() function.