Simple Counting Question

[jesusoneez]

I know how to use DCount to count the number of occurences of a particular "thing" in a field of data, but how do you count the number of occurences of a number across a record?

For example, I have a simple database to record golf scores (attached). I want to count in the statistics area of the form, how many holes were shot in 2, 3, 4, 5 and 6+. Obviously, the results are stored in fields headed Hole1, Hole2...Hole 18.

I can't figure it out, hopefully someone can help me with this...

 

[Fear Naught]

I am not sure that you can do it with DCount() however I have written a sub routine that will do it for you. Copy the following into the code for the form:

Private Sub CountShots()

    Dim int2shots As Integer
    Dim int3shots As Integer
    Dim int5shots As Integer
    Dim int4shots As Integer
    Dim int6shots As Integer
    Dim intCount As Integer
    Dim intTemp As Integer
    
    int2shots = 0
    int3shots = 0
    int4shots = 0
    int5shots = 0
    int6shots = 0
    intCount = 1
    intTemp = 0
    
    Do While intCount < 19
        
        intTemp = Me.Controls("txtHole" & intCount)
        
        Select Case intTemp
            Case 2
                int2shots = int2shots + 1
            Case 3
                int3shots = int3shots + 1
            Case 4
                int4shots = int4shots + 1
            Case 5
                int5shots = int5shots + 1
            Case Is > 5
                int6shots = int6shots + 1
            Case Else
            
        End Select
        intCount = intCount + 1
    Loop
    
  Me.txtTwoShot = int2shots
  Me.txtThreeShot = int3shots
  Me.txtFourShot = int4shots
  Me.txtFiveShot = int5shots
  Me.txtSixShot = int6shots
  
  Me.Refresh
    
End Sub

In the OnCurrent event of the form put:

Call CountShots

You wil need to change the format of the relevant controls on the form to display Integers only though.

HTH

 

[jesusoneez]

I'll give this a go later. Thankyou for the sharp response.

 

[jesusoneez]

Just confirming that this worked a treat. Thanks, Fear Naught.