Urgent DLookup syntax issue

[Guest]

I am trying to make a DLookup look at two coulmns in a table, find the
matching entry for both columns and display the third coulmn in that row.
(Example: field 1 is car or truck field 2 is chevy or ford field three would
be corvette or taurus)

I am using the following statement (which works for the first criteria)

=DLookUp("[TABLE 1].[field 3]","[TABLE 1]","Forms![Form].[Field 1]=[TABLE
1].[Field 1]" And "'Forms![Form].[Field 2]=[TABLE 1].[field 2]")

Help figuring out which typo I made would be greatly appreciated. I need to
have this running Monday morning!

 

[John W. Vinson]

I am trying to make a DLookup look at two coulmns in a table, find the
matching entry for both columns and display the third coulmn in that row.
(Example: field 1 is car or truck field 2 is chevy or ford field three would
be corvette or taurus)

I am using the following statement (which works for the first criteria)

=DLookUp("[TABLE 1].[field 3]","[TABLE 1]","Forms![Form].[Field 1]=[TABLE
1].[Field 1]" And "'Forms![Form].[Field 2]=[TABLE 1].[field 2]")

Help figuring out which typo I made would be greatly appreciated. I need to
have this running Monday morning!

The third argument to any of the Domain function should be a SINGLE
text string (typically concatenated from pieces) which evaluates to a
valid WHERE clause. The word AND needs to be part of that SQL string;
as it is, it's outside the quotes and won't work the way you expect.

Try

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = Forms![Form].[Field 1]
And [field 2] = Forms![Form].[Field 2]")

It's not necessary to qualify the fieldnames with a tablename since
there's only one table; the criterion is typically (though not
required to be) to the right of the fieldname; and if you're passing
JET the full form reference it can be inside the quotes.

In other contexts, you may want to pass the value of the form control
rather than its name. If so, concatenate the value into the string:

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = " & Forms![Form].[Field
1] & " And [field 2] = " & Forms![Form].[Field 2])

If the control named [Field 1] contains 3, and [Field 2] contains 834,
this string will evaluate to

[Field1] = 3 And [Field2] = 834

If one of the fields is of Text datatype, you need ' mark delimiters:
e.g.

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = " & Forms![Form].[Field
1] & " And [field 2] = '" & Forms![Form].[Field 2] & "'")

That's a singlequote after [Field 2] =, and a singlequote surrounded
by doublequotes before the closing parenthesis.




John W. Vinson [MVP]

 

[Guest]

John,

In my glee at getting this thing to work (and I've been wrestling with it,
among other issues, for two months) I forgot to TELL you it worked. Thanks so
very very much - it took all I had not to shout with glee when the thing
popped up with a real status instead of an #Error response. Hurray!!

--Rebecca

I am trying to make a DLookup look at two coulmns in a table, find the
matching entry for both columns and display the third coulmn in that row.
(Example: field 1 is car or truck field 2 is chevy or ford field three would
be corvette or taurus)

I am using the following statement (which works for the first criteria)

=DLookUp("[TABLE 1].[field 3]","[TABLE 1]","Forms![Form].[Field 1]=[TABLE
1].[Field 1]" And "'Forms![Form].[Field 2]=[TABLE 1].[field 2]")

Help figuring out which typo I made would be greatly appreciated. I need to
have this running Monday morning!

The third argument to any of the Domain function should be a SINGLE
text string (typically concatenated from pieces) which evaluates to a
valid WHERE clause. The word AND needs to be part of that SQL string;
as it is, it's outside the quotes and won't work the way you expect.

Try

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = Forms![Form].[Field 1]
And [field 2] = Forms![Form].[Field 2]")

It's not necessary to qualify the fieldnames with a tablename since
there's only one table; the criterion is typically (though not
required to be) to the right of the fieldname; and if you're passing
JET the full form reference it can be inside the quotes.

In other contexts, you may want to pass the value of the form control
rather than its name. If so, concatenate the value into the string:

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = " & Forms![Form].[Field
1] & " And [field 2] = " & Forms![Form].[Field 2])

If the control named [Field 1] contains 3, and [Field 2] contains 834,
this string will evaluate to

[Field1] = 3 And [Field2] = 834

If one of the fields is of Text datatype, you need ' mark delimiters:
e.g.

=DLookUp("[field 3]", "[TABLE 1]", "[Field1] = " & Forms![Form].[Field
1] & " And [field 2] = '" & Forms![Form].[Field 2] & "'")

That's a singlequote after [Field 2] =, and a singlequote surrounded
by doublequotes before the closing parenthesis.




John W. Vinson [MVP]

 

[John W. Vinson]

John,

In my glee at getting this thing to work (and I've been wrestling with it,
among other issues, for two months) I forgot to TELL you it worked. Thanks so
very very much - it took all I had not to shout with glee when the thing
popped up with a real status instead of an #Error response. Hurray!!

I was going to reply with just a line

#Error#

but decided that I'll rejoice with you instead... <g>

Glad I was able to help.

John W. Vinson [MVP]