Unbound Form Data Source - No Records Selected Using Like

[CT Witter]

I'm creating an unbound form and want to populate the form with data.

The 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

 

[NumbersMax]

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

 

[CT Witter]

Thanks for the suggestion..however double quotes leads to the following:

Error -2147217900 (Syntax error (missing operator) in query expression '(LastName Like ''PROF*'')'.)

 

[NumbersMax]

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

 

[CT Witter]

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

 

[NumbersMax]

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

 

[giacomo]

yep, that's exactly the issue... if you change your SQL to LIKE 'PROF%' it should work for you.

 

[CT Witter]

Thanks for the suggestion...that worked great!! I don't know how I could have missed that in the developer's handbook.



Cheers

CT