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?
 
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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
PatHartmanCommented:
You could try:

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


I'm assuming that SearchResults is the name of the Subform control
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.