Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Count Fields in Form If...

    I need to count the fields in my form that have a value to them. If they dont have a value then I dont want to count them.

    There are twenty of them.

    Here is my code:
    Code:
    Option Compare Database
    Dim TheCount As Integer
    Dim intField As Integer
    
    Private Sub cmdCBD_Click()
    On Error GoTo cmdCBD_Err
        For intField = 1 To 20
            If "BD" & intField < "" Then
                TheCount = TheCount + 1
            End If
        Next intField
    txtCBD.Value = TheCount
    
    cmdCBD_Exit:
        Exit Sub
    
    cmdCBD_Err:
        MsgBox Err.Description
        Resume cmdCBD_Exit
    
    End Sub
    I hope someone can help me ,
    JS



    Edit: BD = first part of the field name. There are 20 BD's BD1, BD2, so on to BD20. I need it to check in all of them and make sure there isnt any thing entered.
    Last edited by JSThePatriot; 05-24-04 at 16:48.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If Not IsNull(intField) Then

    ??
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by Teddy
    If Not IsNull(intField) Then

    ??
    Thanks... I knew what I was looking for but I couldnt think of it so I tried a way around it and failed :-P. I will see if this works.

    Thanks I hope it works for what I want,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay I made that work, but I have a looooong process of code that I know could be shorter.

    here it is...

    Code:
    Option Compare Database
    Dim TheCount As Integer
    Dim intField As Integer
    
    Private Sub cmdCBD_Click()
    On Error GoTo cmdCBD_Err
    
    txtCBD.Value = ""
    TheCount = 0
    
        If Not IsNull(BD1) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD2) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD3) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD4) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD5) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD6) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD7) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD8) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD9) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD10) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD11) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD12) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD13) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD14) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD15) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD16) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD17) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD18) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD19) Then
            TheCount = TheCount + 1
        End If
        If Not IsNull(BD20) Then
            TheCount = TheCount + 1
        End If
    txtCBD.Value = TheCount
    
    cmdCBD_Exit:
        Exit Sub
    
    cmdCBD_Err:
        MsgBox Err.Description
        Resume cmdCBD_Exit
    
    End Sub
    Thanks Teddy!,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try this:

    Code:
    For intField = 1 To 20
       If Not IsNull(me("BD" & intField))
          TheCount = TheCount + 1
       End If
    Next intField
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Thanks Teddy. I was missing that Me() thing. What does that do for the function?

    Thanks again (it worked btw),
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can call an object on a form using forms!form(), or me(). Comes in handy when you have to reference an object via string.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay cool thanks.. I now have another one that has 36 to count so this will be easy . I am actually doing this for a past dbforum user that cant ever seem to state his questions right :-P

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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