So simple! Why can't I get it right?

[Noreene Patrick]

I have a crosstab query that has column headings Reg, OT ,Vac ,Sic, hol, brv and the hours for these fields are the value.

ON my report I want to show REg and OT in their respective columns which I can do, but I want all the other headings thrown together (sum of the hours) in an unbound textbox called Other (since these are paid hours but not worked hours).

I cant seem to get it correct...Ive tried (in the controlsource) =sum([vac] + [sic] + [hol]) and several versions of that. If I only use one of the headings such as vac, then it does great. It wont work when I add in another column heading to sum. I am putting this unbound textbox in the dept footer since I want the hours by dept.

What am I doing wrong?

Thanks, Noreene

 

[GizmoT]

Noreene

Do you have the fields vac, sic and hol in the detail of the report? The fields must be present if they are to be added up.

I think you also need your total to be:

=Sum([sic])+Sum([vac])+Sum([hol])

Hope this helps

 

[Noreene Patrick]

Thanks GizzmoT

I tried that and no values appear in the Other textbox plus I put the fields in the deptfooter and the values show on the report.
Other textbox is completely blank....So, I know these values are coming into the report, but they come under their own heading not totaling in the Other unbound textbox.

Can you suggest something else?

Thanks

 

[GizmoT]

The values should be in the detail rather than the footer, Noreene. This should work.

Have you made sure that you have no null values in your data. Anything added to null is still null.

 

[GizmoT]

Have a look at this - it should help

 

[Noreene Patrick]

GizmoT

I still cant get it to work..I put my fields in the detail section plus I put the individual fields (vac, sic, hol) in the detail section also.

It shows all values for those fields under the appropriate headings but it will not total those fields in the unbound box called Other. It is completely blank.

And yes, I know if the value is null it will return null. I do have some depts that show no value in the vac, sic, etc. fields and that is the way it should be since there are days that no one is absent.

What do I try next?

Thanks

 

[stormin_norm]

You can try this, It fixed a problem on my report:

=nz(Sum([sic]))+nz(Sum([vac]))+nz(Sum([hol]))

 

[Noreene Patrick]

GizmoT
Your report is similar to mine but I am trying to combine those 3 fields into one field...Can you try to do that on your db and see what happens?



Storming_norm

I tried your formula and it gave me the same number of hours in every dept. And I had running sum set at NO. Do you have any idea why it summed this way?

Thanks to both of you

 

[stormin_norm]

okay I was getting confused with some of your statements about what is in the footer section & detail section.

How 'bout in the detail section:
Other [text box??]
Record Source=Nz([Vac],0)+Nz([Sic],0)+Nz([hol],0)+Nz([brv],0)
Sum Records=no

What does that give you?

 

[Noreene Patrick]

Ya'll are the greatest thing since sliced bread!!!!!

It worked, and I thank everyone that helped me...

I LOVE THIS FORUM!!!!!!!!!!!!!

Noreene

 

[Noreene Patrick]

Okay, just one more question.

I am curious as to why there had to be Nz added before each field to get the sum...

Why would it not work by using =sum([vac] + [sic] + [hol])? I just wanted to be clear on the difference.

Thanks, Noreene

 

[GizmoT]

Nz is new to me too, but I looked it up in help and it is a way of getting it to deal with null values.

=sum([vac] + [sic] + [hol]) will work in the detail of the report, because for each entry it will total the fields, but in the footer, you want to add up all the totals for [vac], [sic] etc.... so you must get them added up first.

That is how I think about it in my uneducated way!!!!

 

[Noreene Patrick]

Thanks GizmoT

You certainly sound educated to me!!!!!

 

[GizmoT]

Many long months of trial and error - never had a computer lesson in my life. (which probably explains loads to the more worthy contributors here)!!!

I just wish I had found this forum a LONG time ago.

 

[stormin_norm]

When there are null values in a field, the Sum function produces null also.
I had a report, where some fields added, and others didn't. It only sum'd when each field was populated with a value. As soon as there is a null, bingo, I had a blank field in my report.
I searched the forums and found a thread talking about nz function. And now I include nz whenever I see a sumation fail or need to push zero in null fields.

Like you, I had to learn through trial and error. A bit nerve bending when you want to be further along and the clock arms keep movin'

-stormin.