counting over multiple pages??

[Jerome]

Hi there,

I've got the following situation:

A report (R) with a sub-report (SR) in the detail section.
SR's detail section has the following code in its OnFormat event (I need
to know how many 'M' and how many 'F' there are for the 'W' people):

-----------------------------------------------------------------------
Select Case Me.QUAL_PART
Case "M"
Me.nom_p.FontBold = True
Me.nom_p.FontItalic = False
Case "W"
Me.nom_p.FontBold = False
Me.nom_p.FontItalic = True
Case "P"
Me.nom_p.FontBold = False
Me.nom_p.FontItalic = False
CountPar = CountPar + 1
If Me.sexe = "M" Then
CountM = CountM + 1
Else
CountF = CountF + 1
End If
End Select

Me.FCountPart.Value = CountPar
Me.FcountM.Value = CountM
Me.FcountF.Value = CountF
-------------------------------------------------------------

This works fine when the report only has ONE page! If the list goes on
to a second page, the count values get wrong!? It seems like he's adding
those on the second page twice??

I'm still looking myself to find the error, but any help is greatly
appreciated!!

Thanks,

Jerome

 

[Eric Butts [MSFT]]

Hi Jerome,

Why not just use the Domain Aggregate Function DCount? It's not efficient
but in situations like this it gets the job done. Example:

=DCount("[field name]","[table name]", "[field name] = ' " & Me.textbox20
& " ' And [sexe]=M")


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights



--------------------
| Date: Wed, 09 Jun 2004 15:11:32 +0200
| From: Jerome <[email protected]>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6)
Gecko/20040113
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: counting over multiple pages??
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.reports
| NNTP-Posting-Host: someone.mnhn.lu 158.64.59.192
| Lines: 1
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.reports:140137
| X-Tomcat-NG: microsoft.public.access.reports
|
| Hi there,
|
| I've got the following situation:
|
| A report (R) with a sub-report (SR) in the detail section.
| SR's detail section has the following code in its OnFormat event (I need
| to know how many 'M' and how many 'F' there are for the 'W' people):
|
| -----------------------------------------------------------------------
| Select Case Me.QUAL_PART
| Case "M"
| Me.nom_p.FontBold = True
| Me.nom_p.FontItalic = False
| Case "W"
| Me.nom_p.FontBold = False
| Me.nom_p.FontItalic = True
| Case "P"
| Me.nom_p.FontBold = False
| Me.nom_p.FontItalic = False
| CountPar = CountPar + 1
| If Me.sexe = "M" Then
| CountM = CountM + 1
| Else
| CountF = CountF + 1
| End If
| End Select
|
| Me.FCountPart.Value = CountPar
| Me.FcountM.Value = CountM
| Me.FcountF.Value = CountF
| -------------------------------------------------------------
|
| This works fine when the report only has ONE page! If the list goes on
| to a second page, the count values get wrong!? It seems like he's adding
| those on the second page twice??
|
| I'm still looking myself to find the error, but any help is greatly
| appreciated!!
|
| Thanks,
|
| Jerome
|

 

[Duane Hookom]

Don't use any code and don't use Domain Aggregate functions.
Set the control source of Me.FCountPart
To
=Sum(Abs(QUAL_PART = "P"))
Do the same for others
FcountM=Sum(Abs([Sexe]="M" and QUAL_PART = "P"))
FcountF=Sum(Abs([Sexe]<>"M" and QUAL_PART = "P"))

MCountPart

 

[Jerome]

Great! That works, thanks alot

Jerome