Returning values from a RecordSet

S

Steven

Why does this not return the correct answer:

Dim rs As DAO.Recordset
Dim strSql As String

'Note: I was trying to do it with a variable on a call but kept getting an
error
'with this:
'**********Commented out ************
' strSql = "SELECT Count(*) As vCount, " & _
' "FROM [" & TableName & "] " & _
' "WHERE " & WhereClause

'Since I could not make that work I decided to hard code in the SQL of a
query as:

strSql = "SELECT Count(Batch05.Co) as vCount " & _
"FROM Batch05 INNER JOIN ChartOfAccounts ON (Batch05.Account =
ChartOfAccounts.Account) AND (Batch05.Co = ChartOfAccounts.Co) " & _
"WHERE (((ChartOfAccounts.Special)='AR'));"


Set rs = CurrentDb.OpenRecordset(strSql)

MsgBox rs!vCount

rs.Close
Set rs = Nothing
End Function


If I run the actual saved query I get 3 but with my hardcode the rs!Count is
always -0-. Why will it not return for me the count like the query I made in
the query grid does?

Thank you for your help.

Steven
 
G

Granny Spitz via AccessMonster.com

Steven said:
If I run the actual saved query I get 3 but with my hardcode the rs!Count is
always -0-. Why will it not return for me the count like the query I made in
the query grid does?

Have you tried moving to the first record before reading it?

Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveFirst
MsgBox rs!vCount
 
S

Steven

That did it! Thank you very much.

Granny Spitz via AccessMonster.com said:
Have you tried moving to the first record before reading it?

Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveFirst
MsgBox rs!vCount
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top