Sum on Daily Report

[Vergy39]

I am pulling my hair out in creating a report. The report is fine except for totaling. I need to total all the pending issues for each writer on the far right side of the report and the received, completed, pending on the bottom of the report. Please see the attached and look at the bjwQry_DailyTotals report. I have tried the Expression =Sum([TruePresPending]+[VerbalPending]+[CRLPenidng]) and all it returns is a 3 digit number that represents the total of each pending category. For example if a writer has 9 True pres, 0 verbals, and 2 CRLs pending, the expression would return 902. Any help would be greatly appreciated.

Thanks
David V

 

[pbaldy]

It sounds like the fields are being treated as text. You can either fix that in the source, or use the appropriate conversion function:

=Sum(CLng([TruePresPending])+CLng([VerbalPending])+CLng([CRLPenidng]))

 

[Brianwarnock]

With my initials on the query having contributed to this db before, I thought I ought to find my mistake.

Yep the use of NZ converts all the numbers to strings, never understood that, so stick a Val in front in the Info series of queries or correct as per Paul.

#Pending: Val(nz([bjwQry_PendingCRL].[CRLPending],0))

sorry for the oversight.

Brian

Ps didn't expect my naming convention to indicate my work as opposed to yours to continue, I'm (in)famous.

 

[Vergy39]

With my initials on the query having contributed to this db before, I thought I ought to find my mistake.

Yep the use of NZ converts all the numbers to strings, never understood that, so stick a Val in front in the Info series of queries or correct as per Paul.

#Pending: Val(nz([bjwQry_PendingCRL].[CRLPending],0))

sorry for the oversight.

Brian

Ps didn't expect my naming convention to indicate my work as opposed to yours to continue, I'm (in)famous.

You are famous Brian. Since your help, I have accomplished so much on this database. You are great. But have run into a nother wall with this report. I made the change as you suggested but it changed everything on the report to #NAME? errors. I reversed it and tried paul's suggestion but it returned 123 as the total for all writers. Very confused. Please help.

David V.

 

[Brianwarnock]

I'll have look but will be a while.

brian

 

[Vergy39]

I'll have look but will be a while.

brian

No Problem Brian. I am still trying. The query is still OK, so I may just recreate the report and see what happens.

 

[pbaldy]

If this is for that textbox to the right, you don't want the sum. Try:

=CLng([TruePresPending])+CLng([VerbalPending])+CLng([CRLPending])

Edit: Or Brian's solution of course; I hadn't looked at the file before.

 

[Vergy39]

If this is for that textbox to the right, you don't want the sum. Try:

=CLng([TruePresPending])+CLng([VerbalPending])+CLng([CRLPending])

Edit: Or Brian's solution of course; I hadn't looked at the file before.

Perfect! Thanks a bunch.

Brian, for some reason the report is OK now. Sorry to send you off on something that wasn't wrong. Not sure what happened. All OK now.

Thanks
David V.

 

[Brianwarnock]

No problem, its better that you keep trying rather than wait to be spoon fed.

Brian

 

[Vergy39]

Well, now I cannot get the column totals across the bottom of the report. I get zero when I use =CLng([bjwtruepresinfo.#received]), or #error when I use =Sum([bjwtruepresinfo.#received]). Any suggestions?

Thanks
David V.

 

[pbaldy]

=Sum([bjwtruepresinfo].[#received])

 

[Brianwarnock]

Actually Paul [bjwtruepresinfo.#received] is the control name, the Sum works in the Report footer but David wants it in the page footer where he gets the error, I'm sure that I must have forgotten something during my 4 tears of retirement but cant think what.

Brian

 

[pbaldy]

It actually works either way in the report footer, because I had tested mine. I didn't realize it would work the way you have it, so I've learned something new today.

In any case, you can't use the sum function in the page footer. I've seen various workarounds, and they all involve code. Here's one:

http://support.microsoft.com/kb/216311

 

[Brianwarnock]

In any case, you can't use the sum function in the page footer. I've seen various workarounds, and they all involve code. Here's one:

http://support.microsoft.com/kb/216311

I thought that must be the case but could not state it with any authority.

Glad your around to sort us out Paul

Brian

 

[pbaldy]

It's teamwork, Brian. You did the heavy lifting earlier. Plus, your solution on this thread is actually the way I would go. I always lean towards fixing the problem at the source rather than working around it later.

 

[Vergy39]

OK, Thanks to the both of you. It is now working. I just moved it to the Report Footer and the total comes up. It is also placed in the area I want it. I appreciate your patience with me as this is my first experience with access other than tracking phone numbers and addresses.

Thanks Again,
David V