DLOOKUP confusion (1 Viewer)

[colinmunnelly]

i have a report based on a query called cnt1. I have palced a parameter in the query asking the user to name a contractor. What i need to do is place information concerning that contractors address details in several text boxes on the report. This information is in another table called CompanyTable. I am trying to use the Dlookup function and having a difficult time trying to understand the function. I think it should be:

=DLOOKUP("AddressLine1","Companytable","Addressline1 = Company)

Company being the field that is from the original query. Hope i you understand.

Col

 

[David R]

Assuming that these are all text fields, and that AddressLine1 is the field in CompanyTable and Company is the parameter:
DLookup("[AddressLine1]","[CompanyTable]","[AddressLine1] = '" & [Company] & "'")

should work (note the single quotes). I haven't tried Dlookup with a parameter query recently, you may have better luck making a popup form with a combo box to choose the contractor from. You could put the address information in a hidden column of the combo and eliminate the need for Dlookup altogether.

 

[Pat Hartman]

Rather than using DLookup() for this purpose, you should change the report's recordsource query to include a join to the CompanyTable to obtain the address info. DLookup()s are extremely inefficient and should not be used in reports or queries unles absolutely necessary. To understand their inefficiency for use with multiple records, think about what they are doing. If the recordsource for your report contains 100 records (pretty small as far as reports go), the DLookup() solution would actually require Jet to run 5 (addr1, addr2, city, st, zip) times 100 or 500! queries to get the address information. The DLookup() runs a separate query for each field you need for each record of the recordset. And what's even worse is that if your CompanyTable is stored in a linked database on a network server, Jet actually needs to send the entire contents of the CompanyTable to the user's PC EVERY time the DLookup() runs. Your network people will curse your name and think that Access is a pig.