VBA SQL using LIKE

[diloft]

wrote SQL statement in ACCESS 2007 using LIKE that will not work. If I create a query with the same function (user input) it works fine. please tell me what I am doing wrong or if there is a setting or something that needs to be installed to work.

sql = "SELECT tablename.fieldName FROM tablename WHERE fieldname LIKE '*" & variablename & "*'"

running the query returns the matches I have. running this sql returns no matches. I have looked on internet for help, write the code exactly as they state and no matches. I am trying to match parts of names (last name or first name). ie: I have 3 ROSS that should show, but sql gives me nothing.

Also, when taking user input, I UCASE the input to the variablename.

 

[plog]

First, instead of executing your SQL statement in VBA--have the VBA print out the SQL statement so that you can make sure that its actually the SQL you want to execute. Possibly what you think is in variablename isn't in there.

If that doesn't work post your actual VBA along with the SQL it contains back here along with the SQL of the query you created to test it.

 

[pbaldy]

How are you using that? As a general rule, JET and DAO like the * as a wildcard, ADO wants %.

 

[bob fitz]

running this sql returns no matches

What code are you ussing to run this SQL

 

[diloft]

this is the code I am using.
Dim localconnection As ADODB.Connection
Dim rsactivity As New ADODB.Recordset
Set localconnection = CurrentProject.AccessConnection

sql = "SELECT TBLActivities.actDateReceived, TBLActivities.actTypeSupport, TBLActivities.actProviderName," _
& " TBLActivities.actProblem, TBLActivities.actTextStatus, TBLActivities.actComments" _
& " FROM TBLActivities WHERE TBLActivities.actProviderName LIKE '*" & UCase(textsearch.Value) & "*'"

rsactivity.Open sql, localconnection

this returns an empty file; no match found.

this is the query I created for testing;
SELECT TBLActivities.actEducator, TBLActivities.actDateReceived, TBLActivities.actTypeSupport, TBLActivities.actProviderName, TBLActivities.actProblem, TBLActivities.actComments, TBLActivities.actTextStatus
FROM TBLActivities
WHERE (((TBLActivities.actProviderName) Like [ ]));

when I enter *ROSS* I get the 3 entries I expect to receive.

 

[pbaldy]

Did you read my post?

 

[diloft]

thank you all for your assistance. Paul, I tried your solution % and it did work this time. I had tried it before with no luck, so I must have had a quote in the wrong place. My code is working as expected and you saved my database.

thanks again all.

 

[pbaldy]

Happy to help!