MS Access VBA SQL Not Working Properly

For some reason my SQL in VBA is showing blank in my forms box. The SQL works fine in a query. I have several other forms like this one and they all work fine but not this SQL for some reason. What's wrong with it?

Thanks for the help.
If Me!txtShowAllSourceJobs = True Then
'This will show all the jobs finshed or not.

 sSQL = "SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit" _
& " FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID" _
& " WHERE (((SourceSku.SourceSkuID) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustFirstNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustLastNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.CustBusNm) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SKUs.SKU) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((Left([SkuNm], 48)) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.dtmAdd) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSku.SourceJobComplete) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) Or (((SourceSkuStatus.Status) Like "" * "" & [Forms]![frmSourceSKUSearch]![SrchText] & "" * "")) ORDER BY SourceSku.SourceSkuID;"

 Forms!frmSourceSKUSearch!SearchResults.RowSource = sSQL
   Me!SearchResults.Requery
 

Open in new window

LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try printing your SQL before assigning it to your RowSource:

Debug.Print sSQL

Post that back here after doing so ...
0
Dustin StanleyEntrepreneurAuthor Commented:
SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID WHERE (((SourceSku.SourceSkuID) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustFirstNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustLastNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.CustBusNm) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SKUs.SKU) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((Left([SkuNm], 48)) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.dtmAdd) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSku.Sou
rceJobComplete) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) Or (((SourceSkuStatus.Status) Like " * " & [Forms]![frmSourceSKUSearch]![SrchText] & " * ")) ORDER BY SourceSku.SourceSkuID;

Open in new window

0
PatHartmanCommented:
You could try:

   Me.SearchResults.RowSource = sSQL
   Me.SearchResults.Requery


I'm assuming that SearchResults is the name of the Subform control
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If that was posted after you create the sSQL variable, then your form references aren't working correctly. Try this instead (see the items highlighted in Bold):

sSQL = "SELECT SourceSku.SourceSkuID, SourceSku.CustFirstNm, SourceSku.CustLastNm, SourceSku.CustBusNm, SKUs.SKU, Left([SkuNM],48) AS SkuName, SourceSku.dtmAdd, SourceSku.SourceJobComplete, SourceSkuStatus.Status, SourceSku.dtmEdit" _
& " FROM SKUs INNER JOIN (SourceSku LEFT JOIN SourceSkuStatus ON SourceSku.SourceSkuStatusID = SourceSkuStatus.SourceSkuStatusID) ON SKUs.SkuID = SourceSku.SkuID" _
& " WHERE (((SourceSku.SourceSkuID) Like '*" & [Forms]![frmSourceSKUSearch]![SrchText] & "*')) Or (((SourceSku.CustFirstNm) Like '*" & [Forms]![frmSourceSKUSearch]![SrchText] & "*'))

And continue doing the same for all of your Form refereces ...
0
Dustin StanleyEntrepreneurAuthor Commented:
Pat:
Me.SearchResults.RowSource = sSQL
   Me.SearchResults.Requery

Does the same effect.

Scott:
That does not work in the query. In my search box on my form I have the query criteria in each field value as:
Like "*" & [forms]![frmSourceSKUSearch]![SrchText] & "*"

Open in new window


The code I provided in the question is running in a Afterupdate event of a Check box.  I don't get it as I have many search forms just like this one but for some reason this exact SQL is giving me issues.
0
Dustin StanleyEntrepreneurAuthor Commented:
I figured it out. The code was supposed to look like this:
Like "*" & [forms]![frmSourceSKUSearch]![SrchText] & "*"

Open in new window


But for some reason there were spaces in the "*" like this:
Like " * " & [forms]![frmSourceSKUSearch]![SrchText] & " * "

Open in new window


There for once I removed the spaces it worked fine. Thank you two for the help!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's pretty much what I told you to do ...
0
Dustin StanleyEntrepreneurAuthor Commented:
You stated:
 '*" 

Open in new window


But I needed:
 "*" 

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You have to enclose your String values in either Single or Double Quotes. I chose Single quotes, but whatever works for you ...
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.