That's what these lines were for:I really appreciate yall helping me out.
I did the Debug.Print and might have spotted the problem, just not sure how to fix it even though i'm no longer getting any errors.
If you look at the IN clause it has a comma in front of each
SELECT * FROM dbo_tblPrintCenter_SGI WHERE dbo_tblPrintCenter_SGI.hull IN (,'2618') <--- shouldn't be a comma there???
AND dbo_tblPrintCenter_SGI.SSPhase IN (",'001'")<--- shouldn't be a quotation and comma there???AND dbo_tblPrintCenter_SGI.CalcSS BETWEEN 1/1/2016 AND 7/10/2019
strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
What should they be? When i have those lines in and the user makes the selection to ALL of the criteria, it works fine. However if the user only selects 1 or 2, i get an error.
Invalid procedure call or argument
Below is what i have:
strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
strSQL = " SELECT * FROM dbo_tblPrintCenter_SGI WHERE "
If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND "
If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.WS IN (" & strCriteriaWS & ") AND "
If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.LeadDept IN (" & strCriteriaLeadDept & ") And "
If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.SSPhase IN (" & strCriteriaPhase & ") AND "
strSQL = strSQL & "dbo_tblPrintCenter_SGI.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
' strSQL = strSQL & "dbo_tblPrintCenter_SGI.CalcSS BETWEEN (" & txtCalcSSBegin & ") AND (" & txtCalcSSEnd & ")"
'Debug.Print strSQL
Me!qryformsubform.Form.RecordSource = strSQL
End Sub
Update:
I added an IF statement to try and handle if the user selects anything or not however if the user leaves one of the list boxes blank, nothing is returned. I need it to assume if user leaves a list box blank then they want to show everything.
If Me!cmboHull.ItemsSelected.Count > 0 Then strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1) Else strCriteriaHull = "dbo_tblPrintCenter_SGI.hull Like '*'"
If Me!cmboWS.ItemsSelected.Count > 0 Then strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1) Else strCriteriaWS = "dbo_tblPrintCenter_SGI.WS LIKE '*'"
If Me!cmboLeadDept.ItemsSelected.Count > 0 Then strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1) Else strCriteriaLeadDept = "dbo_tblPrintCenter_SGI.LeadDept Like '*'"
If Me!cmboPhase.ItemsSelected.Count > 0 Then strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1) Else strCriteriaPhase = "dbo_tblPrintCenter_SGI.Phase Like '*'"
This is what the debug.Print is showing:
SELECT * FROM dbo_tblPrintCenter_SGI WHERE dbo_tblPrintCenter_SGI.hull IN ('2618') AND dbo_tblPrintCenter_SGI.WS IN (dbo_tblPrintCenter_SGI.WS LIKE '*') AND dbo_tblPrintCenter_SGI.LeadDept IN ('00','01','07') And dbo_tblPrintCenter_SGI.SSPhase IN ('001') AND dbo_tblPrintCenter_SGI.CalcSS BETWEEN #1/1/2016# AND #7/11/2019#
In my test I left the WS (cmboWS field) blank and notice how the SQL statement is including it in the WHERE clause when it probably needs to drop it or maybe select all WS listed in the list box
When you conditionally add each field to the WHERE clause, also trim the comma.
Code:If Len(strCriteriaHull) > 0 Then strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1) strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND " End If
That did the trick. Thank you!!!When you conditionally add each field to the WHERE clause, also trim the comma.
Code:If Len(strCriteriaHull) > 0 Then strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1) strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND " End If
Happy to help!