I am hoping I can get some help with a date parameter that isn't cooperating.
I have a parameter form that feeds a headcount report. Among the various parameter possibilities on the parameter form is a combination box (Combo132) containing operators (>, <, >+, etc) and an unbound text box (Text131). The combination of these two are used as criteria for the field (Term_Date) in the query (Frm_REVIEW_EE) that feeds the report (RPT_REVIEW_EE).
I cannot get this to work right. If I input paramters (Ex. > 1/1/2015) it will return all records with a term date greater than 1/1/2015 correctly. The problem is when I leave the parameter blank.. It still returns records but only if there is a term date in the [Term_Date] field. I cannot figure out how to make it handle no parameter entry for Term_Date.
The full coding behind the OnClick of the parameter form's command button are pasted below. strCrit17 is the item in question. Any help is much appreciated. As a side note, I had a similar issue with [Hire_Date] or strCrit16 but seemed to have figured it out but no go with [Term_Date] or strCrit17. I have messed around with it a few times in the hope that something would work but this is the current view of the coding
Private Sub Command126_Click()
Dim strCrit1 As String
Dim strCrit2 As String
Dim strCrit3 As String
Dim strCrit4 As String
Dim strCrit5 As String
Dim strCrit6 As String
Dim strCrit7 As String
Dim strCrit8 As String
Dim strCrit9 As String
Dim strCrit10 As String
Dim strCrit11 As String
Dim strCrit12 As String
Dim strCrit13 As String
Dim strCrit14 As String
Dim strCrit15 As String
Dim strCrit16 As String
Dim strCrit17 As String
strCrit1 = "[Function] "
If Me!Combo95 <> "All" Then
strCrit1 = strCrit1 & "= '" & Me!Combo95 & "'"
End If
strCrit2 = "[Country_Code] "
If Me!Combo97 <> "All" Then
strCrit2 = strCrit2 & "= '" & Me!Text120 & "'"
End If
strCrit3 = "[Cost_Center] "
If Me!Combo87 <> "All" Then
strCrit3 = strCrit3 & "= " & Me!Text124
End If
strCrit4 = "[Geography] "
If Me!Combo99 <> "All" Then
strCrit4 = strCrit4 & "= '" & Me!Combo99 & "'"
End If
strCrit5 = "[Region] "
If Me!Combo101 <> "All" Then
strCrit5 = strCrit5 & "= '" & Me!Combo101 & "'"
End If
strCrit6 = "[Budget_Region] "
If Me!Combo103 <> "All" Then
strCrit6 = strCrit6 & "= '" & Me!Combo103 & "'"
End If
strCrit7 = "[Legal_Entity] "
If Me!Combo107 <> "All" Then
strCrit7 = strCrit7 & "= '" & Me!Combo107 & "'"
End If
strCrit8 = "[SalaryU$D] "
If IsNull(Me!Combo116) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.Combo116.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & Me!Combo116
End If
If IsNull(Me!Text115) Then
MsgBox "Please put a value in the textbox"
Me!Text115.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & " " & Me!Text115
End If
strCrit9 = "[CommissionU$D] "
If IsNull(Me!CboCommissionOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboCommissionOp.SetFocus
Exit Sub
Else
strCrit9 = strCrit9 & Me!CboCommissionOp
End If
If IsNull(Me!TxtCommission) Then
MsgBox "Please put a value in the textbox"
Me!TxtCommission.SetFocus
Exit Sub
Else
strCrit9 = strCrit9 & " " & Me!TxtCommission
End If
strCrit10 = "[BonusU$D] "
If IsNull(Me!CboBonusOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboBonusOp.SetFocus
Exit Sub
Else
strCrit10 = strCrit10 & Me!CboBonusOp
End If
If IsNull(Me!TxtBonus) Then
MsgBox "Please put a value in the textbox"
Me!TxtBonus.SetFocus
Exit Sub
Else
strCrit10 = strCrit10 & " " & Me!TxtBonus
End If
strCrit11 = "[TotalCompU$D] "
If IsNull(Me!CboTotalCompOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboTotalCompOp.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & Me!CboTotalCompOp
End If
If IsNull(Me!TxtTotalComp) Then
MsgBox "Please put a value in the textbox"
Me!TxtTotalComp.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & " " & Me!TxtTotalComp
End If
strCrit12 = "[Salary] "
If IsNull(Me!Combo116) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.Combo116.SetFocus
Exit Sub
Else
strCrit12 = strCrit12 & Me!Combo116
End If
If IsNull(Me!Text115) Then
MsgBox "Please put a value in the textbox"
Me!Text115.SetFocus
Exit Sub
Else
strCrit12 = strCrit12 & " " & Me!Text115
End If
strCrit13 = "[Commission] "
If IsNull(Me!CboCommissionOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboCommissionOp.SetFocus
Exit Sub
Else
strCrit13 = strCrit13 & Me!CboCommissionOp
End If
If IsNull(Me!TxtCommission) Then
MsgBox "Please put a value in the textbox"
Me!TxtCommission.SetFocus
Exit Sub
Else
strCrit13 = strCrit13 & " " & Me!TxtCommission
End If
strCrit14 = "[Bonus] "
If IsNull(Me!CboBonusOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboBonusOp.SetFocus
Exit Sub
Else
strCrit14 = strCrit14 & Me!CboBonusOp
End If
If IsNull(Me!TxtBonus) Then
MsgBox "Please put a value in the textbox"
Me!TxtBonus.SetFocus
Exit Sub
Else
strCrit14 = strCrit14 & " " & Me!TxtBonus
End If
strCrit15 = "[TotalComp] "
If IsNull(Me!CboTotalCompOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboTotalCompOp.SetFocus
Exit Sub
Else
strCrit15 = strCrit15 & Me!CboTotalCompOp
End If
If IsNull(Me!TxtTotalComp) Then
MsgBox "Please put a value in the textbox"
Me!TxtTotalComp.SetFocus
Exit Sub
Else
strCrit15 = strCrit15 & " " & Me!TxtTotalComp
End If
strCrit16 = "[Hire_Date] "
If IsNull(Me!Combo129) Then
strCrit16 = strCrit16 & "> #1/1/1900#"
Else
strCrit16 = strCrit16 & " " & Me!Combo129 & "#" & Me!Text128 & "#"
End If
strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
strCrit17 = "IsEmpty(" & strCrit17 & ")"
Else
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text136 & "#"
End If
DoCmd.OpenReport " RPT_REVIEW_EE", acViewReport, WhereCondition:=IIf(Me!Combo118 = "U$D", strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7 & " And " & strCrit8 & " And " & strCrit9 & " And " & strCrit10 & " And " & strCrit11 & " And " & strCrit16 & " And " & strCrit17, strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7 & " And " & strCrit12 & " And " & strCrit13 & " And " & strCrit14 & " And " & strCrit15 & " And " & strCrit16 & " And " & strCrit17)
Forms("Frm_REVIEW_Parameter").Visible = False
End Sub
I have a parameter form that feeds a headcount report. Among the various parameter possibilities on the parameter form is a combination box (Combo132) containing operators (>, <, >+, etc) and an unbound text box (Text131). The combination of these two are used as criteria for the field (Term_Date) in the query (Frm_REVIEW_EE) that feeds the report (RPT_REVIEW_EE).
I cannot get this to work right. If I input paramters (Ex. > 1/1/2015) it will return all records with a term date greater than 1/1/2015 correctly. The problem is when I leave the parameter blank.. It still returns records but only if there is a term date in the [Term_Date] field. I cannot figure out how to make it handle no parameter entry for Term_Date.
The full coding behind the OnClick of the parameter form's command button are pasted below. strCrit17 is the item in question. Any help is much appreciated. As a side note, I had a similar issue with [Hire_Date] or strCrit16 but seemed to have figured it out but no go with [Term_Date] or strCrit17. I have messed around with it a few times in the hope that something would work but this is the current view of the coding
Private Sub Command126_Click()
Dim strCrit1 As String
Dim strCrit2 As String
Dim strCrit3 As String
Dim strCrit4 As String
Dim strCrit5 As String
Dim strCrit6 As String
Dim strCrit7 As String
Dim strCrit8 As String
Dim strCrit9 As String
Dim strCrit10 As String
Dim strCrit11 As String
Dim strCrit12 As String
Dim strCrit13 As String
Dim strCrit14 As String
Dim strCrit15 As String
Dim strCrit16 As String
Dim strCrit17 As String
strCrit1 = "[Function] "
If Me!Combo95 <> "All" Then
strCrit1 = strCrit1 & "= '" & Me!Combo95 & "'"
End If
strCrit2 = "[Country_Code] "
If Me!Combo97 <> "All" Then
strCrit2 = strCrit2 & "= '" & Me!Text120 & "'"
End If
strCrit3 = "[Cost_Center] "
If Me!Combo87 <> "All" Then
strCrit3 = strCrit3 & "= " & Me!Text124
End If
strCrit4 = "[Geography] "
If Me!Combo99 <> "All" Then
strCrit4 = strCrit4 & "= '" & Me!Combo99 & "'"
End If
strCrit5 = "[Region] "
If Me!Combo101 <> "All" Then
strCrit5 = strCrit5 & "= '" & Me!Combo101 & "'"
End If
strCrit6 = "[Budget_Region] "
If Me!Combo103 <> "All" Then
strCrit6 = strCrit6 & "= '" & Me!Combo103 & "'"
End If
strCrit7 = "[Legal_Entity] "
If Me!Combo107 <> "All" Then
strCrit7 = strCrit7 & "= '" & Me!Combo107 & "'"
End If
strCrit8 = "[SalaryU$D] "
If IsNull(Me!Combo116) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.Combo116.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & Me!Combo116
End If
If IsNull(Me!Text115) Then
MsgBox "Please put a value in the textbox"
Me!Text115.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & " " & Me!Text115
End If
strCrit9 = "[CommissionU$D] "
If IsNull(Me!CboCommissionOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboCommissionOp.SetFocus
Exit Sub
Else
strCrit9 = strCrit9 & Me!CboCommissionOp
End If
If IsNull(Me!TxtCommission) Then
MsgBox "Please put a value in the textbox"
Me!TxtCommission.SetFocus
Exit Sub
Else
strCrit9 = strCrit9 & " " & Me!TxtCommission
End If
strCrit10 = "[BonusU$D] "
If IsNull(Me!CboBonusOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboBonusOp.SetFocus
Exit Sub
Else
strCrit10 = strCrit10 & Me!CboBonusOp
End If
If IsNull(Me!TxtBonus) Then
MsgBox "Please put a value in the textbox"
Me!TxtBonus.SetFocus
Exit Sub
Else
strCrit10 = strCrit10 & " " & Me!TxtBonus
End If
strCrit11 = "[TotalCompU$D] "
If IsNull(Me!CboTotalCompOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboTotalCompOp.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & Me!CboTotalCompOp
End If
If IsNull(Me!TxtTotalComp) Then
MsgBox "Please put a value in the textbox"
Me!TxtTotalComp.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & " " & Me!TxtTotalComp
End If
strCrit12 = "[Salary] "
If IsNull(Me!Combo116) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.Combo116.SetFocus
Exit Sub
Else
strCrit12 = strCrit12 & Me!Combo116
End If
If IsNull(Me!Text115) Then
MsgBox "Please put a value in the textbox"
Me!Text115.SetFocus
Exit Sub
Else
strCrit12 = strCrit12 & " " & Me!Text115
End If
strCrit13 = "[Commission] "
If IsNull(Me!CboCommissionOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboCommissionOp.SetFocus
Exit Sub
Else
strCrit13 = strCrit13 & Me!CboCommissionOp
End If
If IsNull(Me!TxtCommission) Then
MsgBox "Please put a value in the textbox"
Me!TxtCommission.SetFocus
Exit Sub
Else
strCrit13 = strCrit13 & " " & Me!TxtCommission
End If
strCrit14 = "[Bonus] "
If IsNull(Me!CboBonusOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboBonusOp.SetFocus
Exit Sub
Else
strCrit14 = strCrit14 & Me!CboBonusOp
End If
If IsNull(Me!TxtBonus) Then
MsgBox "Please put a value in the textbox"
Me!TxtBonus.SetFocus
Exit Sub
Else
strCrit14 = strCrit14 & " " & Me!TxtBonus
End If
strCrit15 = "[TotalComp] "
If IsNull(Me!CboTotalCompOp) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboTotalCompOp.SetFocus
Exit Sub
Else
strCrit15 = strCrit15 & Me!CboTotalCompOp
End If
If IsNull(Me!TxtTotalComp) Then
MsgBox "Please put a value in the textbox"
Me!TxtTotalComp.SetFocus
Exit Sub
Else
strCrit15 = strCrit15 & " " & Me!TxtTotalComp
End If
strCrit16 = "[Hire_Date] "
If IsNull(Me!Combo129) Then
strCrit16 = strCrit16 & "> #1/1/1900#"
Else
strCrit16 = strCrit16 & " " & Me!Combo129 & "#" & Me!Text128 & "#"
End If
strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
strCrit17 = "IsEmpty(" & strCrit17 & ")"
Else
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text136 & "#"
End If
DoCmd.OpenReport " RPT_REVIEW_EE", acViewReport, WhereCondition:=IIf(Me!Combo118 = "U$D", strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7 & " And " & strCrit8 & " And " & strCrit9 & " And " & strCrit10 & " And " & strCrit11 & " And " & strCrit16 & " And " & strCrit17, strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7 & " And " & strCrit12 & " And " & strCrit13 & " And " & strCrit14 & " And " & strCrit15 & " And " & strCrit16 & " And " & strCrit17)
Forms("Frm_REVIEW_Parameter").Visible = False
End Sub