[AccessD] Access Record Set Puzzler involving Query withCriteria

I can't duplicate it in Access 2007 either (running in an XP VM)

-- 
Stuart

 On 8 Jan 2011 at 17:23, Brad Marks wrote:

> Stuart,
> 
> Thanks for your help.  Here are answers to your questions.
> 
> 
> What version of Access?
> I am using Access 2007.
> 
> How are you using your Recordset?
> I am simply using a Msgbox to display records that are being returned
> (much like your example). 
> 
> What happens if you use IsNumeric(TestData) as your criteria?
> I tried this and it worked nicely with no problem.  This leads me to
> think that the use of    Like "#####"   is causing the problem.
> 
> 
> I plan to switch to using the "IsNumeric" rather than the   Like
> "#####"  method.  I am still curious why I ran into this problem,
> however.
> 
> Thanks again,
> Brad
> 
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Stuart
> McLachlan Sent: Sat 1/8/2011 4:43 PM To: Access Developers discussion
> and problem solving Subject: Re: [AccessD] Access Record Set Puzzler
> involving Query withCriteria
> 
> 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.
> > 
> > 
> > 
> > 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> 
> 
>