rs.RecordCount returns 1 s/b 1,007 (1 Viewer)

[madel]

This is hopefully a simple error.

I run this code and get 1 record from Debug.Print rsCount

When I run the query I get 1,007
(I copied the output of "Debug.Print queryNameOrSQL" from the immediate window - so I run the same code.)

note: I commenting out "rs.MoveLast" but still got 1.

This is a code snippet of a much larger procedure that loops thru a table and selects each "Area" and outputs to its own folder.

Help!
and thanks,
mike


Sub Test()

Debug.Print "Start: "; Now()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strArea As String
strArea = "Santa Clara Area"

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "SELECT count(Investigations1.Case_No) " & _
"FROM Cases1 LEFT JOIN Investigations1 ON Cases1.Case_No = Investigations1.Case_No " & _
"WHERE " & _
"Cases1.Case_Date_Opened Between DateSerial(2006,1,1) And DateSerial(Year(Date()),Month(Date()),0) AND " & _
"Cases1.Case_Type Not In (""Duplicate"") AND Cases1.Privileged <>""Yes"" and " & _
"Cases1.Area = " & "'" & strArea & "';"

Debug.Print queryNameOrSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
rs.MoveLast
rsCount = rs.RecordCount
Debug.Print rsCount
Debug.Print "End: "; Now()
End Sub

 

[boblarson]

Change this part to use single quotes:

with single quotes

"Cases1.Case_Type Not In ('Duplicate') AND Cases1.Privileged <>'Yes' and " & _

Also, is Privileged a text field or a yes/no field. If yes/no then you would use

"Cases1.Case_Type Not In ('Duplicate') AND Cases1.Privileged <> -1 and " & _

 

[madel]

Bob.

I tried your suggestion and still got 1.

I did mention that I copied and pasted the code from the Immediate Window into MS Access. (Debug.Print queryNameOrSQL) Ran the resolved code and got 1,007. So the code with double quotes should be okay.

However, it is a bit easier to read the code with single quotes, so I'll keep that helpful tip.

thanks.

mike

 

[boblarson]

I don't see anything that should cause a problem. Are these linked tables? And if so are they Access backend or SQL Server backend?

 

[madel]

Not linked. They were imported into Access.
I might understand getting no result - if the query didn't work.
But I can't figure out why I'm getting a 1.

I'll experiment some more.

 

[JANR]

Well you will get only 1 record on a Select Count(...) From TblSomething, how ever this record will have a value of 1007, so a rs.Recordcount will give you 1 and not the value of the count.

JR

 

[madel]

Here is much simpler version of the code.
That, unfortunately, also return 1.

note: TblAreas has 16 entries (see below).

Sub Test()

Debug.Print "Start: "; Now()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "SELECT count(*) as Count FROM tblAreas; "

Debug.Print queryNameOrSQL

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
rs.MoveLast
rsCount = rs.RecordCount

Debug.Print rsCount
Debug.Print "End: "; Now()
End Sub

Immediate Window: SELECT count(*) as Count FROM tblAreas;

Query3Count16

 

[boblarson]

Well you will get only 1 record on a Select Count(...) From TblSomething, how ever this record will have a value of 1007, so a rs.Recordcount will give you 1 and not the value of the count.

JR

Good catch, I missed that one.

 

[JANR]

Function testIT()
Dim rs As DAO.Recordset
Dim strS As String
strS = "SELECT Count(Biler.BNR) AS AntallAvBNR FROM Biler;"
Set rs = CurrentDb.OpenRecordset(strS)
[COLOR=red]Debug.Print rs.Fields(0)
[/COLOR]Set rs = Nothing

End Function

This will give you the count

JR

 

[madel]

Dohh!!!!

So.... I just changed the code to
"SELECT Area as Count FROM tblAreas;" and got 16

Can I get the value of the 1 record from the original SQL? Select Count(*)....."

If so, is there a benefit of doing it one way or the other?

 

[madel]

took too long to post my question. I see that JANR has already posted the answer.

thanks to all.


mike

 

[gemma-the-husky]

out of interest

why do SQL directly?

i generally use a visual query, make sure it works.

then i can just do

dcount("*","mystoredquery") OR
dsum("somefield","mystoredquery") OR

set rs=db.openrecordset("mystoredquery")

occasionally i do need the SQL, and its there if i need it.

with really complex queries the SQL is very complex. Can't think why anyone would do SQL directly, if they had an easy alternative