Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007

    Unanswered: Store the results of a query in a variable?

    Obviously the code below does not work... But it is what I need to be able to do. Is there a way??

    "strResult = db.Execute strSQL ', dbFailOnError" is the line that is wrong. How can I run the query and store the returned value in a variable?

    Sub querytest()
    Dim db As DAO.Database
    Dim strSQL As String, strStatus As String, User As String, strResult As String
    User = CurrentUser
    strStatus = "Completed"
    strSQL = "SELECT Count(*) AS Expr1 FROM Feedback WHERE (((Feedback.Author)='" & User & "') AND ((Feedback.Status)= '" & strStatus & "'));"
    Set db = CurrentDb
    strResult = db.Execute strSQL 
    End Sub

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Hi brent

    You need to populate a recordset and read the contents of the cell from there. There is sample recordset code in the automate excel code I know you have been referred to.

    Alternatively, if you are just trying to get a simple count you could use DCount. For a singleton lookup like this it is more efficient than a recordset. Have a look in help for the specific syntax.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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