Text box control source question 1

[JAES]

I have the following formula in a text box. I know it isn't really close but it is the eaisest way to explain my needs.

I would like a total of all [actRegHours] from the table tabActivity where [actActivity] equals "Court".

=IIf([actActivity]="court",Sum([actRegHours])," ")

Thanks, Jeff

 

[Kiwiman]

Have a look at the dsum function

Take it Easy
Man with one chopstick go hungry

 

[JAES]

I tried many versions of this and I'm still getting "#Error" on the report.

=DSum("[actRegHours]","[queChiefReport1]","[actActivity]= 'Court'")

FYI, this text box is in the group footer of a report.

Any help is appreciated.

 

[JoeAtWork]

Enter the following in the Immediate window and see if it returns a result - at least that will tell you if it's a syntax error.

?DSum("[actRegHours]","[queChiefReport1]","[actActivity]= 'Court'")

 

[JAES]

OK, the Immediate window is new to me and it looks like I should have found it ages ago. I get a "Run-Time error 2001, You cancelled the previous operation" when I enter the above code and press enter. I entered date() and it showed me the correct date so I'm at least in the right place.

 

[dhookom]

If your "domain" is limited to the records in the report then just set the control source to:
[tt][blue]
=Sum(Abs([actActivity]="court") * [actRegHours])
[/blue][/tt]
This will sum the ActRegHours in the group where actActivity = "court".

The control isn't in the actActivity group footer is it?

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

Thanks dhookom, the text box is in a footer for the individual agent. I'm placing the formula in the control source of the text box. While I didn't get a #error with your suggestion I didn't get any results either(text box is blank).

What I'm trying to do is have a summary report that just shows the total of hours (actRegHours) spent in court, among other things, for each agent. I created a group on the field AgentName. I'm not using the group header or detail sections, just the footer. I'm running the report off a query (queChiefReport1)that has all the columns needed, filtered for the current month.

I tried adding the actActivity and actRegHours to the detail section but still didn't get a result. I even tried to eliminate the query and run the report off the table directly (tabActivity). Maybe I need to start over with a fresh blank page!

 

[dhookom]

You should get at least a 0 in the text box. What makes you think the text box is blank? Have you tried displaying the border so you can for sure see the text box? What is the exact expression you used? Did you set the format property of the text box?

Are you sure the text box is in the group footer and not the page footer?

Duane
Hook'D on Access
MS Access MVP

 

[JAES]

The format property was not set. Sometimes you can't see the trees through the forest. Your formula works great when I make the format "General Number"! I really appreciate everything you and tek-tips does to help. I'm off to the contribute $$ page again.

Jeff