Where clause giving mixed results

[Mark Schirle]

I am having a problem with a query.
A form with a button to run the following procedure
A table with 3 fields. Autonumber, Test (True/False) and TestCode
(Text)
4 records in the table
All records contain testcode = 5
3 records contain test = False
1 record contains test = true
When I run the procedure the msgbox says recordcount = 4
When I copy the strsql from the debug.print into a query the recordcount
= 1
What am I doing wrong?

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblTest WHERE test = True and testcode =
'5'"
Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
MsgBox .RecordCount
End With

End Sub

 

[Tom Ellison]

Dear Mark:

Now, the correct answer is 1, right? You have only one row where test =
True. So, when you run the query, copying the SQL from the immediate pane
to a new query, it gives 4 rows? And at least 3 of these have Test = False?
Or do all 4 of them have Test = False, and the True row is being omitted?

Please try "WHERE test <> 0" as an alternative. Does this help? In any
case, 0 is defined as false, all others as true.

When your results show that the query works wrong as a pure query, there's
certainly something wrong. In Jet, I think I remember using the constant
vbTrue, or was that only in vb code? However, for portability, I switched
to <> 0 and = 0 for testing many, many moons ago. Intrinsic constants are
some of the first things to avoid if you want portability.

By the way, your debugging technique is quite good. : )

Tom Ellison

 

[Mark Schirle]

Yes, the correct answer is 1. When I run the query in code I get 4. When I copy
the query and paste it into a query grid I get the correct answer of 1. I have
tried replacing the test = true with a variety of other criteria but I get the
same results. I have fixed the problem by adding a .movelast prior to
..recordcount but I still don't understand why I'm getting these results. This
was code that had been tested and worked for months. It just suddenly stopped
working. I thought maybe my copy of access had become corrupt but I found the
same results on several other installs. I will take your advice and switch to
<>0 or =0.
Thanks

 

[Wayne Morgan]

A recordset isn't "fully populated" until you move to the last record.
However, while the count may be inaccurate without the MoveLast, I would've
expected it to be low, not high. It's nice to know that it could be off
either direction.