[AccessD] Access Record Set Puzzler involving Query withCriteria

I did... it was on a hunch.  His issue is the # is not a wildcard
character in ADO.

Function testwildcards()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Query1", CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
If rs.EOF = False Then
    rs.MoveFirst
    Do Until rs.EOF = True
        MsgBox rs.Fields(0).Value
        rs.MoveNext
    Loop
Else
    MsgBox "No Records"
End If
rs.Close
Set rs = Nothing
End Function
Function testwildcards2()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
If rs.EOF = False Then
    rs.MoveFirst
    Do Until rs.EOF = True
        MsgBox rs.Fields(0).Value
        rs.MoveNext
    Loop
Else
    MsgBox "No Records"
End If
rs.Close
Set rs = Nothing
End Function

Created a little temp table myself, with Query1 having LIKE "###" for
the criteria.  (have one record, in a text field, with 3 numeric
characters).  So testwildcards returns no records. Testwildcards2
returns 1 (As it should).  Changed Query1 to use 4*, returns one record,
as it should, in testwildcards2, returns no records in testwildcards.
Change Query1 to use Like "4%" and the results are the opposite.

This is kind of interesting.  I think this means that ADO is actually
reading the SQL of Query1 and interpreting the SQL itself.  Go figure.

I can't find numeric wildcards for ADO, just % and _ which are * and ?
in Jet.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Saturday, January 08, 2011 6:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access Record Set Puzzler involving Query
withCriteria

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
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.