[RESOLVED] Blank Recordset from Query with Data

[ChrisR873]

Based on Bruce's suggestion here's what I tried. The first is my orginal.

1.
Like "#4#######"
Brings in all 9-digit numbers with a 4 in the second character spot.
2.
Like "%4%%%%%%%"
Brings in all 9-digit numbers with a 4 in any character spot.
3.
Like "*4*******"
Brings in all 9-digit numbers with a 4 in any character spot.
4.
Like '[0-9]1%'
Does nothing... Unless I misunderstood this.

So the only one that does what I want is the first one. So this seems like the only option to go with.

I am using an ADO connection to populate a recordset in MS Access 2000 VBA.

Example data in the Query
Number     -     Name     -     Date
012345678     Sample1     5/31/07
013456789     Sample2     5/31/07
023456789     Sample3     5/31/07
024567890     Sample4     5/31/07
045678901     Sample5     5/31/07
046789012     Sample6     5/31/07
145678901     Sample7     5/31/07
245432109     Sample8     5/31/07

Query Criteria
Number:
Like "#4#######"

Date:
=#5/31/07#

Results are...
Number     -     Name     -     Date
045678901     Sample5     5/31/07
046789012     Sample6     5/31/07
145678901     Sample7     5/31/07
245432109     Sample8     5/31/07

Recordcount = 4

My example code is below.

'... Code Example Snippet
Dim rstExample As ADODB.Recordset
Set rstExample = CreateObject("ADODB.Recordset")
 
rstExample.CursorLocation = adUseClient
rstExample.Open "qryExample", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 
msgbox rstExample.recordcount 'Recordcount shows empty
'... Code Example Snippet

The above code returns no records, but there should be 4 records.

I have tried applying the criteria to the SQL statement when opening the recordset, but same issue. Shows no records because of the LIKE statement.
ie. SQL= "SELECT * FROM qryExample WHERE number LIKE '#4#######'"

So instead of dwindling over this for the past few days. I just wrote a work around that loops through each record and filters the records based on having a 4 in the second spot. But I would still like to solve the issue on why this happens. Any comments or assistance is welcomed.

Thanks,
~Chris