Dlookup to for each record

[shwin]

Hey Guys,

I have a problem here which should be simple but I am stuck and cannot find help on the previous posts on the forum. I have a report which I want to pull some information from a query. The query works fine but the report is giving me an error when I try to do the following Dlookup function. The report field has the following in its control source:

=DLookUp("ApproxValue5yr","ApproxValue","FundName=Forms!ApproxValue!FundName")

ApproxValue5yr is the field name in the query, ApproxValue is the query name

Additionally, if I leave out the "FundName=Forms!ApproxValue!FundName", it gives me the sum total of all the ApproxValue5yr fields. This should be pretty easy but I am not getting it. Any help would be grateful!

THanks!

 

[WayneRyan]

shwin,

=DLookUp("ApproxValue5yr","ApproxValue","FundName = '" & Forms!ApproxValue!FundName & "'")

Wayne

 

[Pat Hartman]

Depending on the size of your report, it could be substantially faster to use a join rather than a DLookup(). By using a DLookup(), you are telling Jet to run a NEW QUERY for EACH record in the report's recordset. So, if there are 1000 records, the DLookup() will run 1000!!!!! separate queries.

We spend a lot of time talking about normalizing your data but not much time on the use of queries to de-normalize them and turn data back into information.

Except in rare cases, DLookup() is the WRONG choice for use in a query.

 

[shwin]

Still not working

Thanks for your help guys.

Unfortunately, I am still getting #Name? in the field box when I try to do the Dlookup. Pat, this database will have only a few records so I think Dlookup may be appropriate. I have no idea what to do now so any help would be great. Again, my query is working fine and gives a correct value for each record however I am unable to show this in my reports. The report is instead just giving the sum of the total number.

Thanks!

 

[Pat Hartman]

Despite the small nature of the recordset, this is not a place to use DLookup(). Try the join.

Just create the query that joins the lookup table to the table currently used as the form's recordsource. Select all the fields you need from the form from BOTH tables. Open the form in design view and replace the table name (or query) in the RecordSource with the name of the new query. You should see the new "lookup" field in the field list. Go to the control where you are trying to use the DLookup() and select the new field as the controlsource rather than the DLookup().