[AccessD] Access Record Set Puzzler involving Query with Criteria

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.