[AccessD] Access Record Set Puzzler involving Query with Criteria

Instead of   "Select * from qry_Test" what happens if you use   just
"qry_Test".  And is this the record source for the Form?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Saturday, January 08, 2011 2:13 PM
To: Access Developers discussion and problem solving
Subject: [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.