Results 1 to 5 of 5

Thread: counting status

  1. #1
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49

    Unanswered: counting status

    Hey all,

    I have a report that I am working on.

    In the report are regular records but on each record is a companystatus field that is either 1, 2, or 3. I have the records sorting no problem and it looks great but my boss wants to have the total of each status at the top/header of the report. I am stumped on this. I imagine that it is pretty easy but I can not figure it out. i am using one query for the one report. The code I am using in te sub header is below if it helps. Once I have these totals I will be done with report. Any and all help is appreciated. Thanks.
    Richard.


    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If CompanyStatus = "1" Then
    txtCompanyStatus = "NOT STARTED"
    Else
    If CompanyStatus = "2" Then
    txtCompanyStatus = "IN PROCESS"
    Else
    txtCompanyStatus = "COMPLETED"
    End If
    End If
    End Sub

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    There are several different ways to do this....here is one:
    Place three TextBoxes into the Report Header and Name them ItemCount1, ItemCount2, and ItemCount3. Put whatever you like into their respective Labels.

    Your report code should now look something like this:

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       Static Cnt1 As Long
       Static Cnt2 As Long
       Static Cnt3 As Long
     
       Select Case CompanyStatus
          Case "1"
             txtCompanyStatus = "NOT STARTED"
             Cnt1 = Cnt1 + 1
          Case "2"
             txtCompanyStatus = "IN PROCESS"
             Cnt2 = Cnt2 + 1
          Case "3"
             txtCompanyStatus = "COMPLETED"
             Cnt3 = Cnt3 + 1
       End Select
       Me.ItemCount1 = Cnt1
       Me.ItemCount2 = Cnt2
       Me.ItemCount3 = Cnt3
    End Sub
    Normally this sort of code would go into the OnFormat event of the Details section of the report but who know how you've set up your report.

    .
    Last edited by CyberLynx; 08-22-06 at 23:34.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    Thanks for the help. I have a question though.

    I see that you put

    Static Cnt1 As Long

    in there but I don't see CNt1 in the code.

    I have tried this a couple of times now and it counts 1 for the first record and does not continue. I think I did something wrong.

    Richard.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Boy oh boy...was I ever sleepin or what. Sorry. The Code is corrected now.

    The code should go into the OnFormat event for the section that hold the txtCompanyStatus field.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Aug 2004
    Location
    Cinci, OH
    Posts
    49
    Thanks very much. I have some results now. To get to work I had to change it just a little. In the 'companystatus header' I have the following code:

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Select Case CompanyStatus
    Case "1"
    txtCompanyStatus = "NOT STARTED"
    Case "2"
    txtCompanyStatus = "IN PROCESS"
    Case "3"
    txtCompanyStatus = "COMPLETED"
    End Select
    End Sub


    In the 'detail' section I have the following code:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Static Cnt1 As Long
    Static Cnt2 As Long
    Static Cnt3 As Long
    Select Case CompanyStatus
    Case "1"
    Cnt1 = Cnt1 + 1
    Case "2"
    Cnt2 = Cnt2 + 1
    Case "3"
    Cnt3 = Cnt3 + 1
    End Select
    Me.ItemCount1 = Cnt1
    Me.ItemCount2 = Cnt2
    Me.ItemCount3 = Cnt3
    End Sub

    Both of the events are under 'On Format'. Now the problem i am having is right now I have 96 records. Out of those records 0 are 'Not Started. 7 are 'In Process' and 89 are 'Completed'.

    My results for the count are not correct with the code:
    ItemCount1 = 0 (which seems correct)
    ItemCount2 = 8 (not correct)
    ItemCount3 = 106 (not correct)

    I can not figure out where it is getting those numbers.

    Thoughts??


    Richard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •