Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count record... (6.0)

    have this mdb.
    I filtre record with this code, how to count ONLY the record filled from PROVA13 based the filtre in PROVA1...
    Example:
    prova1 with "4500" have 14 records
    prova13 have only 12 records filled
    in this case the myvar_count=12

    ....

    Private Sub UserForm_Activate()

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=GCD01F4500DATIPUBBLICAINPS_WEBSTORICO_INPS. MDB;Persist Security Info=False"
    rs.Open " select * from INPS_02 ORDER BY PROVA1", cn, adOpenStatic, adLockReadOnly

    rs.Filter = "PROVA1 = '" & ENTER_PROVA1 & "'"

    'CONTA_RECORD = rs.RecordCount("PROVA13")

    ScrollBar1.Max = rs.RecordCount

    If Not rs.EOF Then
    Call FillTextBoxes
    Else
    MsgBox "NESSUNA RATA PER L'AGENZIA " & ENTER_PROVA1
    Unload Me
    Exit Sub
    End If
    Call CARICA_COMBO
    End Sub
    ....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count record... (6.0)

    You could open a recordset based on the SQL string

    SELECT Count(PROVA13) AS Cnt FROM INPS_02 WHERE PROVA1="4500"

    The value of Cnt is the value you're looking for.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count record... (6.0)

    You dont want to use the literal value 4500, but the vartiable ENTER_PROVA1, and within the string, you must use single quotes around a text value:
    <code>
    rs.Open "SELECT Count(PROVA13) AS Cnt FROM INPS_02 WHERE PROVA1='" & ENTER_PROVA1 & "'", cn, adOpenStatic, adLockReadOnly
    myvar_count = rs.Fields("Cnt")
    </code>

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count record... (6.0)

    ????? correct
    rs.Open " SELECT Count(PROVA13) AS Cnt FROM INPS_02 WHERE PROVA1="4500", cn, adOpenStatic, adLockReadOnly

    aaahhhhh....

    '" & ENTER_PROVA1 & "'"

    is the value for 4500, in this case...

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count record... (6.0)

    Great!!!!!!!!
    Without vb 6.0=Wizard

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count record... (6.0)

    This was a question about databases, so I didn't need VB6 to answer it. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count record... (6.0)

    Hans why the code count all record in PROVA13 i would want to count only filled record...count if PROVA1=4561
    Private Sub CONTA_TOT()


    rs2.Open "SELECT Count(PROVA13) AS Cnt FROM INPS_02 WHERE PROVA1='" & ENTER_PROVA1 & "'", cn, adOpenStatic, adLockReadOnly

    CONTA_RISP = rs2.Fields("Cnt")

    Label41.Caption = CONTA_RECORD
    Label49.Caption = CONTA_RISP
    Label51.Caption = CONTA_RECORD - CONTA_RISP

    rs2.Close
    Set rs2 = Nothing

    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count record... (6.0)

    You have two different kind of blank values in PROVA13: null values where the field contains nothing at all, and empty strings "". Null values are not included in Count(PROVA13), but empty strings "" are included, because they are not really blank. To get around this, you can use
    <code>
    rs2.Open "SELECT Count(*) AS Cnt FROM INPS_02 WHERE Not Trim(PROVA13 & '') = '' AND PROVA1='" & Enter_Prova1 & "'", cn, adOpenStatic, adLockReadOnly</code>

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count record... (6.0)

    Nice...work!

Posting Permissions

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