Unbound Form Data Source - No Records Selected Using Like

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
I'm creating an unbound form and want to populate the form with data.

The code:

Code:
Private WithEvents mrst As ADODB.Recordset

Private Sub Form_Load()
   On Error GoTo Form_Load_Error

    Set mrst = New ADODB.Recordset
    mrst.ActiveConnection = strConnectionString
    mrst.Source = strSQLView
    mrst.CursorType = adOpenStatic
    mrst.LockType = adLockBatchOptimistic
    mrst.CursorLocation = adUseClient
    mrst.Open Options:=adAsyncFetch

   On Error GoTo 0
   Exit Sub

Form_Load_Error:
    Call ErrorLog("Form_frmView", "Form_Load", "VBA Document", Erl, _
    Err.Number, Err.Description)

End Sub



strSQLView ="SELECT * FROM tblHeader WHERE (LastName = 'PROFESSIONAL') "
works correctly and 131 Records are returned

However,

strSQLView ="SELECT * FROM tblHeader WHERE (LastName Like 'PROF*') "

fails and 0 Records returned

This statement SELECT * FROM tblHeader WHERE (LastName Like 'PROF*') works fine when placed into a query

Any suggestions?

Thanks,
CT
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I was just reading up on SQL stuff...my first suggestion is, try double quotes "" : Like "Prof*"

Meanwhile, I'll look a little further to see what I can dig up!
Max
 
Upvote 0
Thanks for the suggestion..however double quotes leads to the following:

Error -2147217900 (Syntax error (missing operator) in query expression '(LastName Like ''PROF*'')'.)
 
Upvote 0
The only other thing I could think of is splitting up the comparison like:

Where Lastname like 'Prof' & '*' or
Where Lastname like 'Prof' & "*"

I couldn't find where I read about that but I think it said that * is treated differently under certain circumstances...I guess maybe this is one of those circumstances.
Max
 
Upvote 0
Tried both of those and no luck. It just doesn't make sense that the SQL works fine in a query using Like but fails when using in a recordset.

Wonder if my connection string has to do with this?

Private Const strConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\shared_drive\XX_be.mdb;Jet OLEDB:Database Password=1234;"

CT
 
Upvote 0
Maybe this will help from my Access 2000 developer's handbook:

"Acces and DAO don't always agree on the use of wildcard characters in SQL strings. If you have a query that uses a LIKE clause, with a "*" or "?" wildcard, Access correctly converts these into the "%" and "_" that ADO wants to see. If you do the same thing directly in the RowSource property of a list box, however, ADO will treat those characters as literals, and the whole thing won't work correctly. Yes, we could have written the code to handle this case, but it would have more than doubled the size of the class. You're welcome to add this code, if you like-it's a matter of parsing the SQL string, looking for the WHERE clause, and replacing "*" with "%" and "?" with "_". On the other hand, it's simple enough to say, "We only handle wildcards in queries, not in the RowSource property," and let it go at that."

I wonder if some of the elements they describe are what you are dealing with?
Max
 
Upvote 0
yep, that's exactly the issue... if you change your SQL to LIKE 'PROF%' it should work for you.
 
Upvote 0
Thanks for the suggestion...that worked great!! I don't know how I could have missed that in the developer's handbook.

:biggrin:

Cheers

CT
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top