sum() dlookup's on report group?

[madEG]

Hello,

I have a report that returns timesheet info on employees. The group-by is on the employee, and the detail records are projects billed-to, including how much time billed to each project by that employee.

Parts of the detail line are accomplished by dlookups, and I was hoping to sum() these values on each employee/group, in the employee footer area.

Per the image, the circles show where I hope to show the sum'd value from text boxes indicated by the arrows...



In the group footer on employee I've set text boxes to:

=Sum(DLookUp("ProjectLeaveHours","qryTimeRpt_LeaveOnly"))

...basically taking the dlookup accomplished in the detail line and trying to sum() it... No luck.

=Sum([RADefaultHours])

...using the name of the control where the dlookup is accomplished prompts me to declare the value of the "RADefaultHours" - indicating the report text box control doesn't know what I'm talking about...

=Sum([Reports]![rptTimesheetReport]![RADefaultHours])

...trying the above again, but hoping to properly declare what is being sum'd... No luck.


...I saw some posts regarding setting "running sum over group" - and I tried that on the text box in the footer where the sum() is being attempted, but no luck.

Can I do this this way? Perhaps I need to set some other variable that is more agreeable than a 'textbox with a dlookup' to do the sum when grouping?

Any ideas?

Thanks!
-Matt G.

 

[MSAccessRookie]

Have you looked at DSum()? It is a companion Function to DLookup(), and might be what you are looking for.

-- Rookie

 

[CJ_London]

What you need to do is to put your Dlookups into your report recordsource so they come through with a field name as part of the query. Assign that in your detail section as a bound field then use

=Sum([RADefaultHours])

in your footer section where RADefaultHours is the name of the controlsource (i.e. the field name brought through in the query).

 

[madEG]

Thanks CJ. I forgot to mention that some of the data points on the detail line are from one database, while others are from another... I wasn't able to write a query well enough to get all items in the recordsource for the report (in a single query), which is why I had to use dlookups in the first place to look into other accompanying queries that do the calculations - but otherwise, I agree. Thanks for looking in!

 

[Pat Hartman]

Domain functions should not be used this way. They are extremely inefficient but the real issue in the case of reports and queries is that they run a completely separate query for EVERY SINGLE RECORD IN THE RECORDSOURCE!!!!! So, if your report includes a thousand records, each domain function is running 1,000 queries!!!

Create totals queries that summarize the data you want to display. Save them and add them to the report's RecordSource query. the database engine is far more efficient about summarizing data than running potentially thousands of domain functions.

For the lookup data, do the same thing. Add either a query or a table to your main query and select the lookup field values.