Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    24

    Unanswered: Counting instances of a field value in VBA

    Hi,

    I have a number of fields on my form, and each one accepts a value of 0,1 or 2:-

    Opening
    UseOfHold
    Pauses
    CallControl
    DeliveryTimes
    CarelineReferral
    CustomersName
    CustomersNeeds
    QuestionsAnswered
    ActiveListening
    Clarity
    Knowledgeable
    EnthusiasticPolite
    Interruption
    EscalateToTM
    Mispronunciations
    Grammar
    Phreaseology
    SpeedOfSpeech

    I need to apply a grade in terms of how many '1's are on the record. So essentially, I need to count the instances of '1' for the above fields so that I can then apply logic to it.

    Please can someone point me in the right direction? I'm a newbie to VBA and can usually cope by fumbling around but this one is out of my depth

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If these are the only Textbox controls on the form, you could use:
    Code:
    Dim ctl As Control
    Dim HowMany1s  As Long
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextbox Then
            If ctl.Value = 1 Then HowMany1s = HowMany1s  + 1
        End If
    Next ctl
    If there are other Textbox controls on the form, you'll need a way to identify those concerned by the counting process. One solution would consist in putting something in their Tag property (here the value used is "Cnt"):
    Code:
    Dim ctl As Control
    Dim HowMany1s  As Long
    
    For Each ctl In Me.Controls
        If ctl.Tag = "Cnt" Then
            If ctl.Value = 1 Then HowMany1s = HowMany1s  + 1
        End If
    Next ctl
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    24
    I've just tested it now and it works like a charm (your 2nd suggestion was the one i needed). Thanks so much for your help!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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