Results 1 to 5 of 5

Thread: row count

  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Arrow Unanswered: row count

    how do i use sql COUNT(*) to find the number of rows and put it in a variable?
    thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    Try something like:

    Code:
        Dim oCnn As ADODB.Connection
        Dim oRst As ADODB.Recordset
        Dim sSQL As String
        Dim lngMyVariable As Long
    
        Set oCnn = CurrentProject.Connection
        Set oRst = New ADODB.Recordset
        sSQL = "SELECT COUNT(*) AS myCount FROM myTable"
        
        oRst.Open sSQL, oCnn
            lngMyVariable = oRst!myCount
        oRst.Close
        oCnn.Close
    
        ' You can assign the variable to anything you want, but I just send it 
        ' to a message box
    
        msgbox lngMyVariable
    
        Set oRst = Nothing
        Set oCnn = Nothing
    HTH.

  3. #3
    Join Date
    Apr 2004
    Posts
    15
    Thanks a lot.
    It is possible to get the value wihout using ADODB or ADO?

  4. #4
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    If you want to assign the COUNT to a form control, you can build an Expression and in the Control Source.

    =DCOUNT("<FieldName>", "<TableName>", "<Where Condition>")


    =DCOUNT("myField1","myTable","[myField2]=1")


    Perhaps you could provide more detail of what you are trying to do.

  5. #5
    Join Date
    Apr 2004
    Posts
    15
    thanks....
    the dcount works well

Posting Permissions

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