Simpler Code For my Criteria 2

[ZaZa]

Hi and Help Please,

I have a form (called SelectionForm)with two combo boxes and one multiselect list box that I use to select criteria to open a report (called Rpt03) via a button.Rpt03 is based on a query that refers to the main table of information.

The combo boxes are:[SelectStatus] and [SelectPriority]
and the multiselect list box is called [SelectCSU]

I have used several lines of code comprising mainly of If Then statements to allow for the case where some or all of the criteria are null. This works fine but now my users want me to ADD FOUR (4) EXTRA COMBOBOXES to the SelectionForm and I shudder to think what the final code will be!! I can do it if I have to but I know that my approach is very primative and I'm praying that a guru out there will have a SHORTER, MORE ELEGENT approach to this problem!

The extra combo boxes are called :
[Discipline] [Engineer] [Contractor] [Area]

Please take a look and you will understand......

In the code, the fields [StatusID], [Priority] and [SystemID] are the related fields on the main table as follows:

SelectionForm Main Table
[SelectStatus] [StatusID]
[SelectPriority] [Priority]
[SelectCSU] [SystemID]
--------------------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria As String

Set cntl = Me.SelectCSU
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then

If Not IsNull(Me![SelectPriority]) Then

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & &quot; [StatusID] = &quot; & Me![SelectStatus] & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria = strCriteria & &quot; [StatusID] = &quot; & Me![SelectStatus] & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem

Else

strCriteria = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;

End If

Else

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & &quot; [StatusID] = &quot; & Me![SelectStatus] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria = strCriteria & &quot; [StatusID] = &quot; & Me![SelectStatus] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem

Else

strCriteria = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;

End If

End If

Else

If Not IsNull(Me![SelectPriority]) Then

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & &quot; [Priority] = &quot; & Me![SelectPriority] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria = strCriteria & &quot; [Priority] = &quot; & Me![SelectPriority] & &quot; And [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem

Else

strCriteria = &quot;[Priority] = &quot; & Me![SelectPriority] & &quot;&quot;

End If

Else

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria = strCriteria & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria = strCriteria & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem

Else

MsgBox &quot;No criteria selected. Displaying all records.&quot;

End If


End If

End If

DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria


'Clear the selections after the report is opened
Dim i As Integer

For i = 0 To (SelectCSU.ListCount - 1)
SelectCSU.Selected(i) = False
Next i

DoCmd.Close acForm, &quot;SelectionForm&quot;, acSaveYes
End Sub

-------------------------

There must be another way! Can you imagine having to add four more combo boxes as selection criteria into that monster above?? Yikess!

ZaZa

 

[Maquis]

ZaZa, Here's how I do it. If the user leaves the combo/list box null just don't include it in your criteria. You can then build your criteria step by step just by checking each box.

You appear to be on the right track with your code, but you only need to check each box once. Just add another variable called something like strAnd and set it equal to &quot;&quot; when you start your code. After setting your first Where statement set strAnd = &quot; and &quot; and then always attach it to the criteria.

See thread702-189120 for an example. (last post) Maq B-)
<insert witty signature here>

 

[MichaelRed]

Maquis is correct -as far as he goes. If you are going to have (Up to) seven seperate criteria all &quot;anded&quot; together, you will NOT in general get any results. You should probably include some provision to let the user know there is a null resultset for the query -other than just showing an empty grid.

In my implementation of users selecting multiple criteria, I just construct the se[erate criteria causes for each item. they all 'look' the same, either empty strings or ~~~~ [field & LogicalOPerator] & value where LogicalOperator ~~ <=, <, =, > >= or <>).

When all have been constructed, I just loop through the set and concatenate w/ the the relational operator (AND). In most implementations, I limit the number of criteria clauses to three, as I find MOST users are not really capable of usefully implementing more than this.

It is also reasonably possible to use drag & drop to construct elaborate criteria. Using this approach allows users to be somewhat more flexible in the ad-hoc development. It takes a little more to get started, but once you get the technique figured out, the processing is actually easier (at least for me). You can provide limits to the values (to those in the field) and have the flexibility of different logical and relational operators as well as different ordering of the criteria clauses. Providing Parens provides different grouping of the criteria clauses. You can come quite close to the query grid capability with very little of the complexity.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZaZa]

Hi,

I have tried ( I think) Marquis's suggetsion of ignoring the criteria if the user did not select the items in the combo box and I have been trying to redo the 2 coboboxes and 1 list box to give me the proper criteria but I am having the following problem.

If I select
[selectStatus] = Open
[selectPriority] = A
[SelectCSU] = 23 and 25 and 26.

I want the following StrCriteria
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=25 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=26

What I am getting is:
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR [SelectCSU]=25 OR [SelectCSU]=26

Below is the code that I have so far. can sopmeone please have a look and tell me what I should be doing?

Private Sub SortRptByDisc_Click()

'This will limit the report to only the CSU's chosen.

Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String
Dim StrCriteria As String

Set cntl = Me.SelectCSU
lngCounter = 0



If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria2 = &quot; [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;
End If


If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria3 = &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria3 = &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem
End If


MsgBox StrCriteria = strCriteria1 & &quot; and &quot; & strCriteria2 & &quot; and &quot; & strCriteria3


'Open Report
DoCmd.OpenReport &quot;Rpt04-PunchItemsGroupByEng&quot;, acViewPreview, , StrCriteria


'Clear the selections after the report is opened
Dim i As Integer

For i = 0 To (SelectCSU.ListCount - 1)
SelectCSU.Selected(i) = False
Next i

DoCmd.Close acForm, &quot;Frm09-SelectCSUForRpt01&quot;, acSaveYes
End Sub

 

[ZaZa]

I Hit the submit button too fast.Here I go again.


Hi,

I have tried ( I think) Marquis's suggetsion of ignoring the criteria if the user did not select the items in the combo box and I have been trying to redo the 2 coboboxes and 1 list box to give me the proper criteria but I am having the following problem.

If I select
[selectStatus] = Open
[selectPriority] = A
[SelectCSU] = 23 and 25 and 26.

I want the following StrCriteria
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=25 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=26

What I am getting is:
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR [SelectCSU]=25 OR [SelectCSU]=26

Below is the code that I have so far. can someone please have a look and tell me what I should be doing?

--------------------------------------.

Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strCriteria3 As String
Dim StrCriteria As String

Set cntl = Me.SelectCSU
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria2 = &quot; [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;
End If


If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria3 = &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;'&quot;
Else
strCriteria3 = &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or&quot;
End If
Next varItem
End If


MsgBox StrCriteria = strCriteria1 & &quot; and &quot; & strCriteria2 & &quot; and &quot; & strCriteria3


'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , StrCriteria


'Clear the selections after the report is opened
Dim i As Integer

For i = 0 To (SelectCSU.ListCount - 1)
SelectCSU.Selected(i) = False
Next i

DoCmd.Close acForm, &quot;SelectionForm&quot;, acSaveYes
End Sub
-----------------------

I'm sure that the error is in the part that says &quot; For each varitem in cntl.ItemsSelected....&quot; but that is all I have been able to discern.

Thanks for responding,
zaZa

 

[MichaelRed]

[selectStatus] = Open
[selectPriority] = A
[SelectCSU] = 23 and 25 and 26.

I THINK YOU want the following StrCriteria
([SelectStatus]= Open AND [selectPriority] = A) and ([SelectCSU]=23 OR
[SelectCSU]=25 OR
[SelectCSU]=26)

So,
What I am getting is JUST MISSING THRE PARENS around the third clause?:
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR [SelectCSU]=25 OR [SelectCSU]=26
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZaZa]

Michael,

I'm afraid you lost me a little. I think you want me to put some parens around parts of the criteria ....?Please explain further if you can.

Sorry for being so thick about this.

ZaZa

 

[MichaelRed]

Look at this carefully. The Parens Act to Group the clauses.

([SelectStatus]= Open AND [selectPriority] = A) and ([SelectCSU]=23 OR
[SelectCSU]=25 OR
[SelectCSU]=26)

So here, the [DelectStatus] and [selectPriority] become ONE criteria, while the set of [SelectCSU] items become another criteria, and these two clauses are joined.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ZaZa]

Hi Michael,

I beleive I am beginning to get your message but I just don't see how to incorporate it into my code. I am trying to work on your's amd Marqui's suggestion of using separate criteria and &quot; anding&quot; them together at the end .

Remember, I want to get the follwowing criteria :
If I select
[selectStatus] = Open
[selectPriority] = A
[SelectCSU] = 23 and 25 and 26.

I want the following StrCriteria
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=25 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=26

The use of strCriteria1 , strCriteria2 and strCriteria3 in my code has taken me one step nearer to my goal. When I check it in the debug window, strCriteria1 appears correct. So does StrCriteria2.

Criteria1 gives: [SelectStatus] = Open And [Priority]=A
Criteria2 gives: CSU=23 OR CSU=25 OR CSU=26

but I still can't join the two together to get the result that I want. For strCriteria3 where would I put parens?

Please take a look if you can and tell me what you think in simple sentences so I can follow.

---------------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String 'Variable to store StatusID
Dim strCriteria2 As String 'Variable to store Priority
Dim strCriteria3 As String 'Variable to store CSU

Set cntl = Me.SelectCSU
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;
Else
strCriteria1 = &quot;&quot;
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria1 = strCriteria1 & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;
Else
strCriteria1 = strCriteria1
End If

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' &quot;
Else
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or &quot;
End If
Next varItem
End If

strCriteria3 = strCriteria1 & &quot; And &quot; & strCriteria2

'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria3

'Clear the selections after the report is opened
Dim i As Integer

For i = 0 To (SelectCSU.ListCount - 1)
SelectCSU.Selected(i) = False
Next i

DoCmd.Close acForm, &quot;SelectionForm&quot;, acSaveYes
End Sub
---------------------------------

Thanks
ZaZa

 

[Maquis]

Zaza, I believe you are mis-stating how you want the criteria to look.

Here's how you are picturing it:
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=25 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=26

Here's how Michael and I are picturing it:
[SelectStatus]= Open and [selectPriority] = A and
([SelectCSU]=23 or [SelectCSU]=25 or [SelectCSU]=26);

Much simpler. The parentheses eliminate the redundancy in the query.
Maq B-)
<insert witty signature here>

 

[ZaZa]

Marquis thanks for respondinig,

I kind of figured that I wasn't communicating clearly but then I thought maybe you guys DID understand BUT I just didn't understand Michael's post very well.

Your and Michael'r idea is wrong. I want
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=23 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=25 OR
[SelectStatus]= Open AND [selectPriority] = A and [SelectCSU]=26

IN other words, I want to print a reoprt FOR EACH OF THE SELECTED CSU's WHERE ([SelectStatus]= Open AND [selectPriority] = A )

In other words the criteria ([SelectStatus]= Open AND [selectPriority] = A ) MUST APPLY FOR ALL OF THE SELECTED CSU's .


Thanks for thinking about this,
ZaZa

 

[Maquis]

That's what the parentheses are for!!!

We are both writing the exact same query which will select the exact same records. We're just writing it differently.

For example, going back to math class:
2 + (3*4) = 2 * 3 + 2 * 4 = 14
5(6+14) = 5*6 + 5*14 = 100
One method is just a shorter way of writing it.

Maq B-)
<insert witty signature here>

 

[ZaZa]

Yipee!!! NOW I GET IT! THANKS A LOT MARQUIS AND MICHAEL!

I finally got the parnes thing! :~/
I finally got the parens thing! s-)
I finalyy got the parens thing! :~/
I finally got the parnes thing! s-)
I finally got the parens thing! :~/
I finalyy got the parens thing! s-)

------------------------------
Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String 'For Status and priority
Dim strCriteria2 As String 'Variable to store CSU
Dim strCriteria3 As String 'To join strCritera1 & 2

Set cntl = Me.SelectCSU
lngCounter = 0

If Not IsNull(Me![SelectStatus]) Then
strCriteria1 = &quot;[StatusID] = &quot; & Me![SelectStatus] & &quot;&quot;
Else
strCriteria1 = &quot;&quot;
End If

If Not IsNull(Me![SelectPriority]) Then
strCriteria1 = strCriteria1 & &quot; And [Priority] = &quot; & Me![SelectPriority] & &quot;&quot;
Else
strCriteria1 = strCriteria1
End If

If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' &quot;
Else
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or &quot;
End If
Next varItem
End If

strCriteria3=strCriteria1 & &quot;And&quot; & &quot;(&quot; & strCriteria2 & &quot&quot;

'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria3
----------------------------------------------


Thanks a lot , both or you for taking the time out to work me out of my thickness!!!!!
You both deserve stars.

Reagards,
ZaZa

 

[ZaZa]

To: MichaelRed, Marquis or ANYONE,

There is now a continuation to the above story....

I am trying to add another textbox called [SelectResolvedDate] which allows the user to get records that were &quot;resolved&quot; (i.e.[StatusID] = Closed) AFTER any date specified .

Ultimately, I want all records that are:
([StatusID]= Me![SelectStatus] OR [ResolvedDate]= Me!SelectResolvedDate) AND [Priority] = Me![SelectPriority] AND ([SelectCSU]=23 or [SelectCSU]=25 or [SelectCSU]=26)

Remember that the value [SelectCSU] is coming from a multi select list box.

The parts in bold are what I am having trouble with as I am not getting the required results. Even when I specify a ResolvedDate, the [ResolvedDate > Me!SelectResolvedDate) part of the criteria is not imposed on the record set.

I have added another strCriteria4 for the bold parts as follows:
----------------------

Dim cntl As Control
Dim lngCounter As Long
Dim varItem As Variant

Dim strCriteria1 As String 'For priority
Dim strCriteria2 As String 'Variable to store CSU
Dim strCriteria3 As String 'To join strCritera1 & 2 &3
Dim strCriteria4 as String 'New variable for Status and ResolvedDate

Set cntl = Me.SelectCSU
lngCounter = 0

'Check the Status
If Not IsNull(Me![SelectStatus]) Then
strCriteria4 = &quot; [StatusID] = &quot; & Me![SelectStatus] & &quot; &quot;
Else
strCriteria4 = &quot;&quot;

'Check the Resolved date
If Not IsNull(Me![SelectResolvedDate]) Then
strCriteria4 = strCriteria4 & &quot; Or [ResolvedDate]> # &quot; & Me![SelectResolvedDate] & &quot; # &quot;
Else
strCriteria4 = strCriteria4


'Check the Priority
If Not IsNull(Me![SelectPriority]) Then
strCriteria1 = strCriteria1 & &quot; And [Priority] = &quot; &
Me![SelectPriority] & &quot;&quot;
Else
strCriteria1 = strCriteria1
End If

'Check the CUS's selected in the list box.
If cntl.ItemsSelected.Count <> 0 Then

For Each varItem In cntl.ItemsSelected
lngCounter = lngCounter + 1
If lngCounter = cntl.ItemsSelected.Count Then
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' &quot;
Else
strCriteria2 = strCriteria2 & &quot; [SystemID] = '&quot; & cntl.Column(0, varItem) & &quot;' or &quot;
End If
Next varItem
End If

'Combine the strcriteria1, strcriteria2 & strcriteria4
strCriteria3= strCriteria1 & &quot;And&quot; & &quot;(&quot; & strCriteria2 & &quot&quot; And &quot; & &quot;(&quot; & strCriteria4 & &quot&quot;

'Open Report
DoCmd.OpenReport &quot;Rpt03&quot;, acViewPreview, , strCriteria3

----------------------------------------
To summarize, I want to get the follwowing criteria :
If I select
[selectStatus] = Open
[selectPriority] = A
[SelectResolvedDate] > 08-apr-02
[SelectCSU] = 23 and 25 and 26.

I want the following StrCriteria
([SelectStatus]= Open or [SelectResolvedDate] > 08-apr-02) AND [selectPriority] = A and [SelectCSU]=23 OR

([SelectStatus]= Open or [SelectResolvedDate] > 08-apr-02) AND [selectPriority] = A and [SelectCSU]=25 OR


([SelectStatus]= Open or [SelectResolvedDate] > 08-apr-02) AND [selectPriority] = A and [SelectCSU]=26

There is something wrong with the way I am writing strCriteria4 (If I leave out the [SelectResolvedDate] part , then everything is fine) but I can't figure it out for the life of me!


Please help, if you can...
ZaZa

 

[Maquis]

Your summary of the select appears correct to me. I didn't take the time though to read through all your code.

For debugging purposes, I would suggest putting a msgbox right before the openreport statement which would display your final criteria string. That way you can take a look and see if the code is producing the SQL string that you think it is. Maq
<insert witty signature here>

 

[MichaelRed]

I suspect the date is not being treated as DATE type, but as String. Construct the date in the std format as in 04/08/02 and suround it with the &quot;#&quot;, to distinguish it from a literal.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over