Multiple conditions in DLookUp

[MStressed]

I am trying to use a DLookUp function in my query, which will be based on two
sets of criteria. Here is what I have in "simple" terms, I will try to
explain it...

Everything has functioned properly. I am now trying to add a column in the
query that will be based on certain criteria from a separate table.

If I write it like this:
DLookUp("[Table1]![fieldName]","Table1","[Table1]![fieldName]=" &
[Table2]![fieldName]) the value becomes as expected.

If I write it like this:
DLookUp("[Table1]![fieldName]","Table1","[Table1]![fieldName2]>" &
[Table2]![fieldName2]) the value becomes as expected.

If I try to combine the two:
DLookUp("[Table1]![fieldName]","Table1","[Table1]![fieldName]=" &
[Table2]![fieldName] And "[Table1]![fieldName2]>" & [Table2]![fieldName2]) I
get #Error.

What I am trying to accomplish is this. I want to look up in a table a
price where "Variable A" falls between a range(two fields built in the table)
and is of specific type(also a field built in the table).

'Product A' is 45" long. I want to find in my "chart"(table) the price of
'Product A' where 45" falls into that specific range (36" - 48") AND based on
what 'Product A' is. For instance, if 'Product A' is "Metal" and is 45"
long, it's price would be $35.00. If 'Product A' is "Metal" and is 74" long,
it's price would be $65.00. If 'Product A' is "Wood" and is 45" long, it's
price would be $40.00.

I hope I explained this correctly. I am thinking the DLookUp is the way to
go, I just can't get the syntax correct. Also, all fields are number type,
including the product type(ID field). Again, I can get it to work with one
condition, but not with two.

Any help is much appreciated.

 

[Ofer]

Try that

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

 

[John Vinson]

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]

 

[Ofer]

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] & "#")

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]

 

[MStressed]

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.

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] & "#")

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]

 

[Ofer]

You wrote the end word twice. Try this:

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

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.

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] & "#")

On Tue, 24 May 2005 12:32:04 -0700, "Ofer"

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]

 

[Ofer]

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

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.

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] & "#")

On Tue, 24 May 2005 12:32:04 -0700, "Ofer"

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]

 

[MStressed]

This does not seem to be working. It appears that it is only evaluating the
first condition of the second set, where it checks if 'fieldName2' is equal
to 'Table2.fieldName2'. If the value of the length of the part falls within
a range, no result is returned. It only returns a value if the fieldName
equals the field in the lookup table. Maybe I am missing some parentheses
somewhere.

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

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.

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] & "#")



:

On Tue, 24 May 2005 12:32:04 -0700, "Ofer"

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]

 

[Ofer]

It sound like you looking for Or condition and not AND
I hope I'm not wrong, but when you write an AND condition if the second
condition will fall then everything else will fall with it. try that instead

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

This does not seem to be working. It appears that it is only evaluating the
first condition of the second set, where it checks if 'fieldName2' is equal
to 'Table2.fieldName2'. If the value of the length of the part falls within
a range, no result is returned. It only returns a value if the fieldName
equals the field in the lookup table. Maybe I am missing some parentheses
somewhere.

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

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.

:

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] & "#")



:

On Tue, 24 May 2005 12:32:04 -0700, "Ofer"

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]