Hey guys I decided my date range should be optional instead of mandatory. I'm now getting an error (Syntax error in Date query expression...). I commented out the part that checks for a blank value and I believe the error is coming from the SELECT statement.
Code:
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!lstHull.ItemsSelected
strCriteriaHull = strCriteriaHull & ",'" & Me!lstHull.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!lstWS.ItemsSelected
strCriteriaWS = strCriteriaWS & ",'" & Me!lstWS.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!lstLeadDept.ItemsSelected
strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!lstLeadDept.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!lstSSPhase.ItemsSelected
strCriteriaPhase = strCriteriaPhase & ",'" & Me!lstSSPhase.ItemData(varItem) & "'"
Next varItem
If Len(strCriteriaHull) > 0 Then
strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
End If
If Len(strCriteriaWS) > 0 Then
strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
End If
If Len(strCriteriaLeadDept) > 0 Then
strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") AND "
End If
If Len(strCriteriaPhase) > 0 Then
strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
strSQL = strSQL & "dbo_tblPrintCenter.Phase IN (" & strCriteriaPhase & ") AND "
End If
'Check for a blank value
' If IsNull(Me![txtCalcSSBegin]) Then
' MsgBox "You must supply a SS Date"
' Cancel = True
' Exit Sub
' End If
'
'
' If IsNull(Me![txtCalcSSEnd]) Then
' MsgBox "You must supply a SS Date"
' Cancel = True
' Exit Sub
' End If
strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ") AND "
If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") And "
If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") AND "
strSQL = strSQL & "dbo_tblPrintCenter.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
DoCmd.SetWarnings False
Set qryDef = db.QueryDefs("qryform")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
'Set the object variables to Nothing to ensurethat these are cleared from the memory
Set db = Nothing
Set qdf = Nothing
Forms![Print Request Search Form]![dbo_tblPrintCenter subform].Form.Requery
End Sub