count values in report

  • Thread starter klp via AccessMonster.com
  • Start date
K

klp via AccessMonster.com

I have a report that I've been constantly working on and have posted several
questions to this particular report. I'm at a new stage and seem to be stuck,
again! This is what I need:

I have a report that will display data using a crosstab query. The text boxes
on my report are all unbound because it's an 12 month rolling report. There
is immense code behind the report to produce all of my data correctly.
Currently my report shows:

Item Desc Mth1 - Mth12 Totals

Everything displays correctly, however I want to add logic in my report to
add data to my new field cnt. I want this field to calculate what months have
values in them. For example:

Item desc May Jun Jul Aug Total Cnt
55555 test 0 5 1 0 6 2

So on and so forth. I have logic on the Detail_Print which is where I believe
it should go - at least that's where the logic to calculate the month totals
and row totals are performed. If you need additional information I'll be glad
to give it. Any direction to take would be great. I'll post the logic I have
if needed.

Thanks Kim P

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim lngRowTotal As Long
Dim lngRowCount As Long
Dim lngRowAvg As Long
Dim NumOfMonths As Integer

' If PrintCount is 1, initialize rowTotal & rowAvg variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
lngRowAvg = 0
lngRowCount = 0

For intX = 3 To intColumnCount
' Starting at column 3 (first text box with crosstab value),
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" +
Format(intX))
Next intX

' Place row total in text box in detail section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal

'Place row avg in text box in detail section
Me("Col" + Format(intColumnCount + 2)) = lngRowAvg

'Place row count in text box in detail section
Me("Col" + Format(intColumnCount + 3)) = lngRowCount

' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub
 
K

klp via AccessMonster.com

FYI - I added logic that partially works. Within this For statement
For intX = 3 To intColumnCount
' Starting at column 3 (first text box with crosstab value),
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
ADDED LOGIC:
If lngRowTotal <> 0 then
lngRowCount = lngRowCount + 1
End if
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" +
Format(intX))
Next intX
It partially works but what it's doing is, it looks at the last month and
counts every month that is shown. Exp.
Jul Aug Total Cnt
15 2 17 2
7 0 7 2
0 5 5 1

Any ideas here?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top