Results 1 to 11 of 11

Thread: Select Count

  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: Select Count

    If a have a Query which counts any article
    SELECT CIUNT(article) from anyTable how could I get this count stored in a variable?

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Select Count

    Originally posted by hammbakka
    If a have a Query which counts any article
    SELECT CIUNT(article) from anyTable; how could I get this count stored in a variable?

    I mean SELECT COUNT(article) from anyTable

    Say it results in 27, how could I get this 27 stored in a variable.
    I know, I can write it in a table then read it from that table. But I don't want to do it that way.

  3. #3
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    set your sql up in a record set and do something like:


    If Not oInputRecordSet.EOF And Not oInputRecordSet.BOF Then
    oInputRecordSet.MoveLast
    oInputRecordSet.MoveFirst
    lngRecordCount = oInputRecordSet.RecordCount
    Else
    lngRecordCount = 0
    End If

    ...rtk

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by rtkracht
    set your sql up in a record set and do something like:


    If Not oInputRecordSet.EOF And Not oInputRecordSet.BOF Then
    oInputRecordSet.MoveLast
    oInputRecordSet.MoveFirst
    lngRecordCount = oInputRecordSet.RecordCount
    Else
    lngRecordCount = 0
    End If

    ...rtk
    Thanks for your reply, but what I need is something else.

    I have a statement like SELECT COUNT (myField) FROM anyTable; it results in a number which is the count of that field.
    What I want is getting this number in a varialbe. I don't use RecordSet

  5. #5
    Join Date
    Mar 2002
    Posts
    32
    One way or another I think you need to use a recordset. This way will give you the results of that SQL statement however.

    dim icount as integer
    dim fieldcount as ADODB.Recordset

    strSQL = "SELECT COUNT (myField) AS myfieldcount FROM anyTable"
    Set fieldcount = New ADODB.Recordset
    With fieldcount
    .ActiveConnection = CurrentProject.Connection
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Source = strSQL
    .Open
    End With

    icount = fieldcount.myfieldcount

    fieldcount.close

  6. #6
    Join Date
    Feb 2004
    Location
    Houston, Texas
    Posts
    45
    Sorry... yes I made a mistake with my code suggestion using a DAO recordset... change .recordcount to oRs.Fields(0).value and you would have your count in a variable.
    ...rtk

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by rtkracht
    Sorry... yes I made a mistake with my code suggestion using a DAO recordset... change .recordcount to oRs.Fields(0).value and you would have your count in a variable.
    ...rtk
    Thank you for your suggestion, I'll try it, I think it functions.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    BenCustalow
    Thanks for your solution, if functios for Access 2000.
    I use Access 97

  9. #9
    Join Date
    Feb 2004
    Posts
    199
    then use DAO instead ADO

    dim icount as integer
    dim fieldcount as DAO.Recordset

    strSQL = "SELECT COUNT (myField) AS myfieldcount FROM anyTable"
    Set fieldcount = CurrentDb.openrecordset(strSQL)
    icount = fieldcount.myfieldcount

    fieldcount.close
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  10. #10
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Kakha
    then use DAO instead ADO

    dim icount as integer
    dim fieldcount as DAO.Recordset

    strSQL = "SELECT COUNT (myField) AS myfieldcount FROM anyTable"
    Set fieldcount = CurrentDb.openrecordset(strSQL)
    icount = fieldcount.myfieldcount

    fieldcount.close
    Thanks that is even better, but I think you should change something


    icount = fieldcount.myfieldcount
    change to
    icount = fieldcount("myfieldcount")
    What do you mean?

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    How about to count all the records in a table, sorry if it isnt what you're trying to do..

    MyRecords = CurrentDb.TableDefs("My_Table").RecordCount
    you can alter this to querdefs if you like..

    Regards
    Ken


    I think that is not correct, because CurrentDb.TableDefs("My_Table").RecordCount

    will result in 1, because you wrote the number of Records in the new table as an only Record.

    Insted use
    CurrentDb.TableDefs("My_Table").Fields("yourFieldI nTheNewTable")

Posting Permissions

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