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!