Criteria calculation performed in Header

[Jsha]

I have a report that I am creating that needs to basically group a totals in the header. This is the last part that I need for this report to be complete and it is racking my brain.

Okay, the report basically looks like this.
(Header)
Activity General Location Hours
Category Area Description tbox1
Category Area Description tbox2

Staff Duties Hours Worked

(Details)
FullName Emp_title tbox3
________

Okay in the detail section tbox3 is an unbound tbox with this formula in it "=Sum([ADSDETAIL_REGHOURS])", which works. Now in tbox1 I an IIF statement to add all the hours up from a group of titles, here is what I had so far "=Sum(IIf([EMP_TITLE]="SUE TECH II",[ADSDETAIL_REGHOURS],0))" this is the latest line of code I have changed it so many times now. tbox2 is going to be the same statement but with different titles.

Can anyone help, please?

 

[dhookom]

Try this:
=Sum(Abs([EMP_TITLE]="SUE TECH II") * ADSDETAIL_REGHOURS)
This assumes ADSDETAIL_REGHOURS is a numeric field in your report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Jsha]

Dhook, I tried that but I am getting an Error. Is there a way I can group the totals in the header though?

 

[dhookom]

group the totals" isn't too clear to me. Are you placing this totals text box in the Page Header by chance?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Jsha]

I must make a clarification, the details are not in the detail section of my report, I have created a FullName footer and I am doing a calculation of total hours there. I want to take the hours of everyone who has a specific job title and add them together. And that number should appear in the Page Header, is this possible?

 

[dhookom]

Doing this in the Page Header is near impossible. It is much easier if you can use a Group or Report Header.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Jsha]

The problem is that I cant even get it to work in the page footer area, I am hoping that if I can at least get it to work there I can simple reference that txtbox in the header. It may not work though I am thinking because the information in the page header populates before the rest of the report. Can you help me get the formula to work atleast?

 

[dhookom]

You can't use [red]any[/red] Page section for this type of calculation. There is a knowledgebase article out there that suggests how to place totals in a Page Footer but I don't think it is worth the work if you can do as I suggested and use a Group or Report Header or Footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Jsha]

Thank you