[AccessD] Access Record Set Puzzler involving Query with Criteria

What version of Access?  How are you using your Recordset?
What happens if you use IsNumeric(TestData) as your criteria?

I can't reproduce this in 2003.

I assume that all numeric fields are 5 digit.

qryTest:
SELECT tblTest.TestData
FROM tblTest
WHERE (((tblTest.TestData) Like "#####"));

Using qyrTest as the recordsource for a form returns all expected records as does this:

Function test()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryTest")
While Not rs.EOF
MsgBox rs(0)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Function

I can send you my test.mdb if want.


-- 
Stuart

On 8 Jan 2011 at 16:12, Brad Marks wrote:

> All,
> 
> I ran into something that seems really strange and has me puzzled. 
> After seeing this situation with "real" data, I decided to re-create
> it with a test table, test data, test query, etc. in order to help
> figure out what is going on.
> 
> I have set up a small Access table that has only one field.  It is a
> Text field, but it should normally contain numeric data.  
> 
> I added 5 records to this test table.  4 of the records have numeric
> data in the one field and 1 record does not have numeric data in the
> one field.
> 
> I set up a query to pull data from the test table.  If I have no
> Criteria (no SQL "Where" statement), I see all 5 records when I run
> the query AND when I use the query as the data source for a Record
> Set.  So far, so good.
> 
> Now things get interesting.
> 
> If I add this Query Criteria     Like "#####"   
> the record with the non numeric field is filtered out when I run the
> query and only 4 records are returned.  This is what I would expect. 
> However, when I now use the Query as the data source for a Record Set,
> NO records are returned.  The Select statement for the Record Set is
> very simple -  "Select * from qry_Test"
> 
> I am puzzled.  I don´t think that I have ever seen a situation where
> the number of records returned via a Record Set (based on a query) are
> different from the number of records returned when the query is run by
> itself. (When there is no Where statement on the Record Set Open)
> 
> Am I missing something here?
> 
> Is this a "Feature" ?  :-)
> 
> Has anyone else ever seen anything like this?
> 
> Thanks in advance for your help.
> 
> Brad
> 
> PS.  The "Real" data originates from an old "legacy" system.  I do not
> have control over how the fields are defined or control over the data
> that is entered into the fields.  That is why I am finding non-numeric
> data in a field that should only contain numeric values. 
> 
> 
>