VBA SQL statement help

[Miked1978]

I have a form where a user can select multiple parameters and the form will return the dataset in a subform. It currently works IF the users selects ALL of the parameters listed but I need to make it work where the user can only select 1 or 2 parameters. Each parameter is in a List drop down box.



So for instance on the form the user may select one or two hulls and will want to return all records for those hulls. However the way I have it written they also must select a "WS', "LeadDept", and "Phase" for the form to return anything.

I'm pretty sure it has to do with my SQL statement not knowing what to do if the user doesn't select anything.


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 dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ")" & _
" AND dbo_tblPrintCenter_SGI.WS IN (" & strCriteriaWS & ")" & _
" AND dbo_tblPrintCenter_SGI.LeadDept IN (" & strCriteriaLeadDept & ")" & _
" AND dbo_tblPrintCenter_SGI.CalcSS BETWEEN '" & txtCalcSSBegin & "' AND '" & txtCalcSSEnd & "'" & _
" AND dbo_tblPrintCenter_SGI.SSPhase IN (" & strCriteriaPhase & ")"


Any help will be greatly appreciated.

[CJ_London]

first thing you will get an error here

strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)

if strCriteriaHull is null or has a length of 1

same for your other assignments

user may select one or two hulls

what do these strings actually look like? and how does the user select them?

might have an impact on the solution

but to answer your question you need to build your criteria separately using if starements perhaps something like

strSQL = " SELECT * FROM dbo_tblPrintCenter_SGI"
strWHERE=""
if nz(strCriteriaHull,"")<>"" then strWHERE=" AND dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ")" 'AND included to simplify the code
if nz(strCriteriaWS,"")<>"" then  strWHERE=" AND dbo_tblPrintCenter_SGI.WS IN (" & strCriteriaWS & ")" & strWHERE
etc.

if strWHERE<>"" then strSQL=strSQL & " WHERE" & mid(strWHERE,5)

[Miked1978]

Here is the beginning of my code. I pieced all of this together from googling different issues as they occurred. I'm sure its not the best way to write VBA.

Private Sub Cmd_RunQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteriaHull As String
Dim strCriteriaWS As String
Dim strCriteriaLeadDept As String
Dim strCriteriaPhase As String
Dim CalcSSBegin As Date
Dim CalcSSEnf As Date
Dim strSQL As String
Dim qryDef As QueryDef





Set db = CurrentDb()
Set qdf = db.QueryDefs("qryform")


For Each varItem In Me!cmboHull.ItemsSelected
strCriteriaHull = strCriteriaHull & ",'" & Me!cmboHull.ItemData(varItem) & "'"
Next varItem



For Each varItem In Me!cmboWS.ItemsSelected
strCriteriaWS = strCriteriaWS & ",'" & Me!cmboWS.ItemData(varItem) & "'"
Next varItem


For Each varItem In Me!cmboLeadDept.ItemsSelected
strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!cmboLeadDept.ItemData(varItem) & "'"
Next varItem


For Each varItem In Me!cmboPhase.ItemsSelected
strCriteriaPhase = strCriteriaPhase & ",'" & Me!cmboPhase.ItemData(varItem) & "'"
Next varItem

[Miked1978]

What should this be? strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)

I used your SQL statement and it works if I have all the criteria selected.

[rpeare]

Private Sub Cmd_RunQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteriaHull As String
Dim strCriteriaWS As String
Dim strCriteriaLeadDept As String
Dim strCriteriaPhase As String
Dim CalcSSBegin As Date
Dim CalcSSEnf As Date
Dim strSQL As String
Dim qryDef As QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryform")

For Each varItem In Me!cmboHull.ItemsSelected
   strCriteriaHull = strCriteriaHull & ",'" & Me!cmboHull.ItemData(varItem) & "'"
Next varItem

For Each varItem In Me!cmboWS.ItemsSelected
   strCriteriaWS = strCriteriaWS & ",'" & Me!cmboWS.ItemData(varItem) & "'"
Next varItem

For Each varItem In Me!cmboLeadDept.ItemsSelected
   strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!cmboLeadDept.ItemData(varItem) & "'"
Next varItem

For Each varItem In Me!cmboPhase.ItemsSelected
   strCriteriaPhase = strCriteriaPhase & ",'" & Me!cmboPhase.ItemData(varItem) & "'"
Next varItem

'you don't need these
'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 & "#"

You have to encapsulate your IN string in quotes
this also assumes txtcalcssbegin and txtcalcssend are dates if they're text values you can go back to your ' marks
you can also use a debug.print strsql before you try to do anythign with it to see where your errors are by pasting the SQL code into a query window and seeing what error it throws

[Miked1978]

I'm getting a Run-Time error '13' Type Mismatch

if len(strcriteriaws) > 0 then strSQL = strSQL & "dbo_tblPrintCenter_SGI.WS IN (""" & strCriteriaWS & """)" AND "

[pbaldy]

I don't see the need for all the quotes. Try

if len(strcriteriaws) > 0 then strSQL = strSQL & "dbo_tblPrintCenter_SGI.WS IN (" & strCriteriaWS & ") AND "

[Miked1978]

That got rid of my error but now nothing is happening when the query runs. When I press the command button on my form the mouse spins like the query is running but i'm not getting any results. Just to be clear I have a subform underneath my mainform that displays the results of the query. The results don't change when I run the query despite selecting different criteria. Below is the code I have thus far.


Private Sub Cmd_RunQuery_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteriaHull As String
Dim strCriteriaWS As String
Dim strCriteriaLeadDept As String
Dim strCriteriaPhase As String
Dim CalcSSBegin As Date
Dim CalcSSEnf As Date
Dim strSQL As String
Dim qryDef As QueryDef


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryform")


For Each varItem In Me!cmboHull.ItemsSelected
strCriteriaHull = strCriteriaHull & ",'" & Me!cmboHull.ItemData(varItem) & "'"
Next varItem


For Each varItem In Me!cmboWS.ItemsSelected
strCriteriaWS = strCriteriaWS & ",'" & Me!cmboWS.ItemData(varItem) & "'"
Next varItem


For Each varItem In Me!cmboLeadDept.ItemsSelected
strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!cmboLeadDept.ItemData(varItem) & "'"
Next varItem


For Each varItem In Me!cmboPhase.ItemsSelected
strCriteriaPhase = strCriteriaPhase & ",'" & Me!cmboPhase.ItemData(varItem) & "'"
Next varItem



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 & "#"

Me!qryformsubform.Form.RecordSource = "qryform"

End Sub

[rpeare]

I think you have to encapsulate the IN part in quotes if you're testing strings against one another don't you?

if len(strcriteriaws) > 0 then strSQL = strSQL & "dbo_tblPrintCenter_SGI.WS IN (""" & strCriteriaWS & """) AND "

Try this instead, I had an extra set of quotes in the line after the closing ) on the IN statement, the other lines will have to be corrected as well assuming removing them entirely does not work.

[rpeare]

also, debug.print your SQL statement and paste it into a query window to see where it bombs out, then address that part in your SQL building statement

[pbaldy]

You've set a SQL string to some SQL but not put it anywhere. Either use the QueryDef you've got to set its SQL property to your SQL, or just set the subform's source to the SQL instead of the query.

Here's a visual on what rpeare is suggesting:

http://www.baldyweb.com/ImmediateWindow.htm

[pbaldy]

I think you have to encapsulate the IN part in quotes if you're testing strings against one another don't you?

Unless I'm misunderstanding, no. The variable strCriteriaWS contains the single quotes around each value.

[Miked1978]

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

[Miked1978]

You've set a SQL string to some SQL but not put it anywhere. Either use the QueryDef you've got to set its SQL property to your SQL, or just set the subform's source to the SQL instead of the query.

Here's a visual on what rpeare is suggesting:

http://www.baldyweb.com/ImmediateWindow.htm

Since I'm not sure how to make the subform read the SQL, how would I use the QueryDef?

[pbaldy]

That's actually easier:

Me!qryformsubform.Form.RecordSource = strSQL

You wouldn't need the QueryDef at all. To use the QueryDef:

qdf.SQL = strSQL