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

    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?

    Code:
    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
    Ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    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
  •