Re: Multiple conditions in DLookUp

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You wrote the 'and' word twice. Instead of the first 'and' you should have
the '&' character.

DLookup("[fieldName]", "Table1", "[fieldName]=" &
[Table2]![fieldName] & "And [fieldName2]=" & [Table2]![fieldName2] & "And
[fieldName3]>" & [Table2]![fieldName2])


"MStressed" wrote:

> Thanks guys! It works as expected. It still works even without the space
> before the word And. That helps out a lot, the only thing is that I have to
> evaluate against three conditions. They are: "Product Type", "Length equal
> to" and "Length less than".
>
> My statement is as such: DLookup("[fieldName]", "Table1", "[fieldName]=" &
> [Table2]![fieldName] & "And [fieldName2]=" & [Table2]![fieldName2])
>
> I want to somehow add a check for [fieldName2]>[Table2]![fieldName2].
>
> I tried - DLookup("[fieldName]", "Table1", "[fieldName]=" &
> [Table2]![fieldName] & "And [fieldName2]=" & [Table2]![fieldName2] And "And
> [fieldName3]>" & [Table2]![fieldName2])
>
> This syntax is my problem, I'm sure. Thanks again.
>
> "Ofer" wrote:
>
> > Tanks John I noticed that, but I thought they will notice that, thanks for
> > replaying.
> > Also incase the criteria is with date then we should add the Hash letter
> > before and after
> >
> > DLookUp("[fieldName]","Table1","[fieldName]=#" & [Table2]![fieldName]
> > & "# And [fieldName2]>#" & [Table2]![fieldName2] & "#")
> >
> >
> >
> > "John Vinson" wrote:
> >
> > > On Tue, 24 May 2005 12:32:04 -0700, "Ofer"
> > > <Ofer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> > >
> > > >Try that
> > > >
> > > >DLookUp("[fieldName]","Table1","[fieldName]=" & [Table2]![fieldName] & "And
> > > >[fieldName2]>" & [Table2]![fieldName2])
> > >
> > > One tiny tweak: you need a blank before the word And:
> > >
> > > DLookUp("[fieldName]","Table1","[fieldName]=" & [Table2]![fieldName] &
> > > " And [fieldName2]>" & [Table2]![fieldName2])
> > >
> > > In addition, if either Fieldname1 or Fieldname2 is of Text type, you
> > > need the syntactically required quotemarks:
> > >
> > > DLookUp("[fieldName]","Table1","[fieldName]='" & [Table2]![fieldName]
> > > & "' And [fieldName2]>'" & [Table2]![fieldName2] & "'")
> > >
> > >
> > > John W. Vinson[MVP]
> > >
> > >
> > >
.

Relevant Pages

  • Re: Multiple conditions in DLookUp
    ... > Also incase the criteria is with date then we should add the Hash letter ... >> need the syntactically required quotemarks: ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Multiple conditions in DLookUp
    ... You wrote the end word twice. ... >> Also incase the criteria is with date then we should add the Hash letter ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Countif/And Function
    ... >> criteria. ... >> this be done using the COUNTIF or AND functions?? ... >> count the number of cells in column E with a value of N in column E AND a ... Prev by Date: ...
    (microsoft.public.excel.misc)
  • Diagonalizing blocks of a unitary matrix
    ... unitary transormation matrix, i.e. there exists a unitary n*n matrix U ... commute with each other, but this does not seem to be sufficient. ... there some criteria, ... Prev by Date: ...
    (sci.math)
  • SUMPRODUCT OR SUMIF? with <= and >=
    ... I've used SUMPRODUCT and SUMIF in the past, ... than" criteria ... ... LavaDude... ... Prev by Date: ...
    (microsoft.public.excel)