Problem With Date Parameter Not Used

state90

Registered User.
Local time
Today, 02:33
Joined
Sep 16, 2014
Messages
65
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
 
Are you trying to return values where there has not been a date entered for that record? If so:

Code:
strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
strCrit17 = "Not IsDate(" & strCrit17 & ")"
Else
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text136 & "#"
End If
 
First, thank you for your help. We are getting there. Where my coding only returned results if there was a term date your coding is returnign results only if there is no term date. To answer your question, I need term_date to be ignored as criteria unless a parameter is specifically entered.
 
One of my attempts was to handle this in the DoCmd line of the coding. Per the coding below, I did this to handle whether USD or Local Currency amounts were visible or not visible and that worked perfectly.

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

I was thinking of adding another IIF function to this line where if the combo129 isnull then I don't include the strCrit17 in the line and if Combo129 is not null then I exclude the strCrit17 from this line?
 
Your coding is a mess, but I guess you know that. Do not persist using the default names like Text376 , but name each control according to what it is. When you return to this in 3 months - and you will - you'll thank yourself.

AS to your criteria: there is no reason to do it in two places like you are attempting. Construct one string of criteria valid at a time, and that is then the total string to pass to the OpenReport. Right now you are doing some here and some there, and the total is ... well .. no comment :D
 
Point well taken. In the name of figuring these out I went with the defaulted names but you are correct, it does pay to re-name these defaults. I got a little lazy on those that I added to the coding recently. As you can see, I use names for most and then went with defaults. Those with default names are just the recent stuff I have added. I will update those once the code works as expected. Another note would be that I know enough to be dangerous! Not necessarily effective but dangerous. I learn from asking the questions when I encounter something I cannot figure out so any guidance is much appreciated. I would love to do it as you suggest but I have no idea how to get it done. Any suggestions on what changes I need to make to the coding to get it to work?
 
Not

crit1
crit2
crit 3678

but MyCriteria= crit1 & crit2 & crit 3678

OpenRecordset ,,,,MyCriteria,,. (count the commas yourself, this is an example)
 
I got the Else in the coding of strCrit17 to work but getting the If portion to include all term date records still won't. The

strCrit17 = "Not IsDate(" & strCrit17 & ")"

results in only those with no dates.

I tried the following coding to get it to work but the coding doesn't work. Any suggestions? My thought process was to equate strCrit17 to those with no dates AND those with dates.

strCrit17 = "Not IsDate(" & strCrit17 & ")" And " IsDate(" & strCrit17 & ")"

Thanks again,

Kurt
 
If it were me, i would follow spike's suggestion and string together the criteria only where the criteria exists, that way you don't need to worry about scenarios like this.

Code:
dim strWhere as String
If isDate(txt) then 
strWhere = strWhere & ...
Else
'Ignore
End If

etc...

For getting the job done though in your scenario, why not just insert some ridiculously early date. Crude i know...

Code:
strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
strCrit17 = strCrit17 & " >#1800#"
Else
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text136 & "#"
End If

Just ensure 1800 is earlier than the earliset date in your db.
 
Thanks Pyro,

The problem is I don't know enough about coding to adjust. The coding I do have in place was built through asking questions but not necessarily because I know enough about the coding...if that makes sense. So, varying off of what was suggested to me in the past and therefore the basis of the coding I currently have in place is probably not going to work.

To me, this is the strangest thing because the coding I have in place now works for the most part. If I enter criteria for Term_Date (such as >= 1/1/2015) it works perfectly. I just cannot get it to return all values if I leave the criteria untouched. It always returns only results with term dates in there. I tried the ' ignore and it still returns only records with term dates in the population. There are considerable records with active employees that have no designated term dates.

Interestingly enough, in my research I created a "Remove Filter" button using the following coding:
Private Sub Command66_Click()
On Error Resume Next
Reports![RPT_REVIEW_EE].FilterOn = False
End Sub

When I click on that button the report does show all employees...with and without term dates.

Here is my coding as is including my attempt at using your ' ignore coding.

strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
' Ignore
Else
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text131 & "#"
End If
 
Sorry...I didn't answer the suggestion of using an absurd date...which is what I did with the Start_Date and that works fine....but that is because every record has a start date. Only employees designated to be terminated have a term date so this doesn't work in dealing with the Term_Date field as it still only returns records with a term date.
 
Ignoring the strings that combine operators (>, <, etc) and a text field, every other string works if left as null. When I view the Filter in the report it appears as follows:

[Function] And [Country_Code] And [Cost_Center] And [Geography] And [Region] And [Budget_Region] And [Legal_Entity] And [SalaryU$D] >= 0 And [CommissionU$D] >= 0 And [BonusU$D] >= 0 And [TotalCompU$D] >= 0 And [Hire_Date] > #1/1/1900# And [Term_Date]

That tells me there is no reason that it shouldn't be working. Take [Country_Code] as an example......No country code was selected so it appears as "[Country_Code]" in the Filter of the report. [Term_Date] appears the same way so why wouldn't it also be returning all records whether a term date is selected or not? Doesn't make sense to me and appears to be beyond my capabilities.
 
Okay...I think I understand what Spike was suggesting....which is really the approach I had taken in the other strings so I don't know what it was about Start_Date and Term_Date that prompted me to think I needed to do it this way! I think it was because my initial coding wasn't working so I dabbled with different approaches? At any rate, I have the following coding for both Start_Date and Term_Date which I believe is what Spike was referencing. The result is the same.....it only returns records that have a date in the term date field.

strCrit17 = "[Term_Date] "
If Not IsNull(Me!Text136) Then
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text131 & "#"
End If
 
Sorry State90 - little kids running around my feet here causing much distraction!

Code:
If IsNull(Me!Combo132) Then
strCrit17 = "([Term_Date] > #1800# Or Not IsDate([Term_Date]))"
Else
strCrit17 = "[Term_Date] " & Me!Combo132 & "#" & Me!Text136 & "#"
End If
 
Yep, that works too

if Text136 is unbound and not checked for a valid date entry, then your user might enter some other value by mistake. So switch IsNull for IsDate.

Code:
strCrit17 = "[Term_Date] "
If Not IsDate(Me!Text136) Then
strCrit17 = strCrit17 & " " & Me!Combo132 & "#" & Me!Text131 & "#"
End If
 
Pyro!!!!! Thank you very much!

The following worked:
strCrit17 = "[Term_Date] "
If IsNull(Me!Combo132) Then
strCrit17 = "([Term_Date] > #1/1/1800# Or Not IsDate([Term_Date]))"
Else
strCrit17 = "[Term_Date] " & Me!Combo132 & "#" & Me!Text136 & "#"
End If

I cannot thank you enough for your patience with me on this!

Kurt
 

Users who are viewing this thread

Back
Top Bottom