dlookup using multiple fields

[Guest]

I am looking for ideas that can help me lookup a value in a table
(tblcustomer) that is based on mutilple fields. Here is an example, an user
will manually enter a bank#,account# and the note# on a form that I created,
and what I want this form to do is return the customer name in a text box by
going to an existing table (tblcustomer) using the combination of Bank#,
Account# and note #. TblCustomer contains these fields: bank#,account#,
note#, Customer name and Customer address. I can do a simple dlookup based on
one single field say just account#, but I don't know how to do a dlookup
based on 3 fields combine. Is CONCATENATING all three fields the best way to
go, or is there any other way I can use that is more efficient. Any help is
very much appreciated.

 

[Allen Browne]

Lisa, for the 3rd argument of DLookup(), you need to supply a string that
looks like the WHERE clause of a query, e.g.:
"([bank#] = 99") AND ([account#] = 88) AND ([note#] = 77)"

To get that result, you will usually be concatenating the values into the
string.

Don't forget to use quotes marks as delimiters if the field is of type Text,
or # as delimiters around date values.

 

[Guest]

A dlookup using few fields in a filter will be writen as follow

=Dlookup("FieldName","TableName","[FieldNumber] = " & Me.FieldNum & " And
[FieldString] = '" & me.FieldStr & "' AND [FieldDate] = #" & me.FieldDate &
"#")