[RESOLVED] Blank Recordset from Query with Data

[ChrisR873]

This is a strange occurance. And I'm lead to believe its because of a like criteria in the query.

When I populate a recordset in vb using this query, that has some 100+ records in it, the recordset appears empty. But when I view the query in access I can see all 100+ records. I can't seem to find any help about this issue on the web. Any suggestions would be helpful.

Thanks,
~Chris

[brucevde]

Change the wildcard character used in the Like criteria from a * to %.

[ChrisR873]

Well, I already just worked around the issue. But I will try a test tomorrow and see if that helps any. Btw, my wildcard is a # for numbers not the * symbol.
For example... Like "#4#######".

I will let you know if that fixes it tomorrow.

~Chris

[brucevde]

The wildcard characters supported depend on the data access technology (ADO, DAO) and the provider or driver you are using to connect to the Access database.

Regardless, if the above fails try

Like '[0-9]1%'

[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

[GaryMazzone]

Is the value your are seaching on a text or number field?

The % sign as a wildcard works like this:
If %4 Returns any thing ending in 4
If %4% Returns any thing with a 4 anywhere in it
If 4% Returns any thing with a 4 at the start.

So if you want a 4 in the second position then you need the single character wild card indicator ( an underscore _ )
If _4% returns any thing with a 4 in the second position followed by any thing.

[ChrisR873]

Thanks Gary, your post did work. Also thanks to Bruce, whose kinda gave me the right answer, but Gary helped explain it better.

The field is a text field being pulled from a table I cannot edit. So what I think happened is that because it is a text field, it makes whatever is inside the field text, even if I want them to be numbers. So I see now using the number wildcard wasn't working because technically they aren't numbers. Thus using the character wildcard works.

Now I can remove my workaround which will improve the reading speed with less record and my query is back to working like a query again! lol.

Thanks for the assistance.
~Chris