Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    16

    Unanswered: SQL Statement problem

    Hi,
    I've a table named "Perguntas" with 58 records. It has the fallowing fields: ID, Titulo, Tipo, Pergunta, Correccao, Cod.

    I'm working in a code to search for all records where Tipo= V/F. I've written this:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim str As String
    Dim nrec As Integer

    Set db = CurrentDb
    str = "SELECT * FROM Perguntas WHERE Tipo='V/F'"
    Set rst = db.OpenRecordset(str)
    nrec = rst.RecordCount

    MsgBox nrec

    rst.Close
    db.Close

    But when i execute the code nrec returns always 1. The table has 58 records, 33 are V/F. nrec should be equal to 33. What is the problem?

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Can't remember exactly...

    why, but once when I was using either ADO or DAO I would always get a record count of <1> and it would bypass my if rstrecordcount = 0 and execute the code. Try a rst.movelast and rst.movefirst and then do the rst.recordcount.

    If that doesn't work then try a 'select count(*)' and use the return value.

    Good Luck, Stu
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this
    Code:
    Set rst = db.OpenRecordset(str)
    rst.MoveLast
    nrec = rst.RecordCount
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha, should have refreshed before replying.

    And yes, using Count(*) will be more efficient
    George
    Home | Blog

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Hey George,

    Any idea of why sometimes the rst.recordcount returns a -1 or something other than the true count? I can't remember in my old code if I did the first/last thing but I do remember it always returning the negative one.

    Thanx, Stu
    --If its free, take it for what its worth!

  6. #6
    Join Date
    Jun 2008
    Posts
    16
    Thank you all for your fast help. It's working i just needed the line
    rst.MoveLast. Now i have all the 33 records.

    Thank you again

  7. #7
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Quote Originally Posted by stuschmied
    Hey George,

    Any idea of why sometimes the rst.recordcount returns a -1 or something other than the true count? I can't remember in my old code if I did the first/last thing but I do remember it always returning the negative one.

    Thanx, Stu
    The recordset is not fully populated until it has gone through all the records.

    C

Posting Permissions

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