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.

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/...UBED1=ACCESS-L



"tom" <(E-Mail Removed)> wrote in message
news:nIL2h.1268$(E-Mail Removed)...
> 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.
>
>
>
>
>
>
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"tom" <(E-Mail Removed)> wrote in message
news:nIL2h.1268$(E-Mail Removed)...
> 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.
>
>
>
>
>
>
>
>

On Fri, 03 Nov 2006 18:16:51 GMT, "tom" <(E-Mail Removed)>
wrote:

>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]

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


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:OdDOof3$(E-Mail Removed)...
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "tom" <(E-Mail Removed)> wrote in message
> news:nIL2h.1268$(E-Mail Removed)...
> > 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.
> >
> >
> >
> >
> >
> >
> >
> >
>
>

& 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).

--
HTH
Van T. Dinh
MVP (Access)



"Tom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank you, I am confused by the &, What is the
> rule for these?
>
>

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


"Van T. Dinh" <(E-Mail Removed)> wrote in message
news:ue5jiL5$(E-Mail Removed)...
>& 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).
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Tom" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thank you, I am confused by the &, What is the
>> rule for these?
>>
>>
>
>

tom wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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.

--
HTH
Van T. Dinh
MVP (Access)



"tom" <(E-Mail Removed)> wrote in message
news:XdP2h.21548$(E-Mail Removed)...
> Thank you, though I still am not clear on why
> they are needed in the dlookup criteria.
>
>