SQL

[Marco]

I have a table with about 20.000 records
2725 record has in Field "new" a letter A
of those 2725 two records have in Field "sold" the value True

SELECT * FROM tblA WHERE New="A" AND sold=True;

Gives a correct result of 2

If I put this Sql-string in some BasicCode like

Dim dbsA As Database, rst As recordset
Set dbsA = Currentdb
StrSql = "SELECT * FROM tblA WHERE New=""A"" AND sold=0; "
Set rst = dbsA.OpenRecordset(StrSql)
MyResult = rst.RecordCount

gives a result of 2725

Why?

Marco

 

[Rodrigo]

You need to move to the end of the recordset before you can tell the record
count.

rst.movelast
myresult = rst.recordcount

Rodrigo.

 

[Dirk Goldgar]

I have a table with about 20.000 records
2725 record has in Field "new" a letter A
of those 2725 two records have in Field "sold" the value True

SELECT * FROM tblA WHERE New="A" AND sold=True;

Gives a correct result of 2

If I put this Sql-string in some BasicCode like

Dim dbsA As Database, rst As recordset
Set dbsA = Currentdb
StrSql = "SELECT * FROM tblA WHERE New=""A"" AND sold=0; "
Set rst = dbsA.OpenRecordset(StrSql)
MyResult = rst.RecordCount

gives a result of 2725

Why?

Marco

Well, first, in the second case you're asking for records where [sold]
is False, not True.

Second, you can't get a reliable value from a Recordset's RecordCount
property (unless it's a table-type recordset) until you have visited the
last record of the recordset:

Set rst = dbsA.OpenRecordset(StrSql)
With rst
If Not .EOF Then
.MoveLast
.MoveFirst
End If
MyResult = .RecordCount
End With