Want to group hours by EmpNum

[bulrush]

(Sorry, I want to SUM hours by EmpNum.)

I have a table, tmpHours, which contains these fields:
EmpNum, PROCNO, HOURS, Payable

It contains multiple entries per person, per day, or hours worked by Process Number (PROCNO). Some PROCNOs are payable, some are not. Some PROCNOs are special, like for a doctor appointment. Table tmpHours also contains all data for 7 days (1 week). The Payable field is a Yes/No field.

I have a report where I want to sum all the hours, per person for all the records in tmpHours (1 week). However, some Process Numbers are payable, some are not, so I need to use critiera.

On my report I group by EmpNum and show the EmpNum footer with these columns/text boxes for types of pay:
txtEmpNum txtWorked txtDoctor txtFuneral

- Putting a Dsum as the recordsource for txtWorkHours did not work. This is the Dsum I tried:
=Dsum("[PROCHOURS]", "tmpHours", "[Payable]=True")

Is there an easier way to do this, perhaps using VBA in my Group_Format event? I was trying this in my Group_Format event:

dim crit as string
dim si as single
' Using table tmpHours:
' Sum hours flagged as Payable for txtWorked for this EMPNO.
crit = ""
crit = "(((tmpHours.EMPNO)='" & txtEmpNum & "') AND ((tmpHours.Payable)=True)) "
si = DSum("[PROCHOURS]", "tmpHours", crit)
txtWorked.Value = si

' Now calc. doctor hours for this person.
crit = ""
crit = "(((tmpHours.EMPNO)='" & txtEmpNum & "') AND ((tmpHours.PROCNO)=3945)) "
si = DSum("[PROCHOURS]", "tmpHours", crit)
txtDoctor.Value = si

txtWorked seem to give the right number, but not txtDoctor. When the code runs for txtDoctor I get an error "Invalid use of null" however the variable crit is not null, it contains the correct string.

Any ideas? Thank you.

 

[Scooterbug]

=Dsum("[PROCHOURS]", "tmpHours", "[Payable]=True")

try using the numerical value of Yes/No (Yes = -1, No = 0)

 

[bulrush]

Ok, when I use
=Dsum("[PROCHOURS]", "tmpHours", "[Payable]=True")

as the ControlSource for txtWorked, I get what appears to be correct hours. But when I use something similar for txtDoctor, I get nulls, even though I checked that some people (namely me) have doctor hours in the table tmpHours. Here's what I used for txtDoctor as the ControlSource:
=Dsum("[PROCHOURS]", "tmpHours", "([EMPNO]='" & txtEmpNum & "') and ([PROCNO]=3945)")

 

[boblarson]

Actually ProcHours may have nulls so a DSUM with nulls will give you an error about nulls.

Nz(DSum("[PROCHOURS]", "tmpHours", crit),0)

And get rid of all of the parentheses. You don't need them. For example change this:

crit = "(((tmpHours.EMPNO)='" & txtEmpNum & "') AND ((tmpHours.Payable)=True)) "


to this

crit = "[EMPNO]='" & txtEmpNum & "' AND [Payable]=True "

You do not need the table name in a domain aggregate if you do not have duplicate names in the query. And if you are using the Domain Aggregate on a table then you would not use the table name in the criteria or the field as you have already specified the table in the table area of the code for the domain aggregate.

 

[bulrush]

Thank you for the Nz hint. I think the problem is solved.

 

[Rich]

=Sum( Iif([Payable],[PROCHOURS],0))