[AccessD] Access Record Set Puzzler involving Query withCriteria

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.