SQL

  • Thread starter Thread starter Marco
  • Start date Start date
M

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
 
You need to move to the end of the recordset before you can tell the record
count.

rst.movelast
myresult = rst.recordcount

Rodrigo.
 
Marco said:
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
Click to expand...

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
 
Back
Top