[AccessD] Access Record Set Puzzler involving Query with Criteria

Tried both ways in A2003 and A2007 and couldn't duplicate Brad's problem.


-- 
Stuart


On 8 Jan 2011 at 15:56, Rocky Smolin wrote:

> 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. 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>