Coding for multiple field selection on forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I've got a form on which users can select one or more criteria for each field (on the test there are two fields) and click the submit button to generate a report that filters based upon the criteria they've specified. I've got it so that it works if they select one or more of the first field and one of the second field, but if they make multiple selection in the second field it fails to work properly (it seems to generate a multiple "Or" criteria based on all the selections). I'm relatively new to coding so I'm unclear what the issue is with the code. I'm posting the code below for reference if anyone can tell me what is wrong with it, thanks in advance for any/all help:
Option Compare Database
Option Explicit


Private Sub cmdOpenMedwatchIndication_Click()
On Error GoTo Err_cmdOpenMedwatchIndication_Click

Dim stDocName As String
Dim Criteria As String
Dim Criteria1 As String
Dim Criteria2 As String

Dim i As Variant
Dim j As Variant


' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Indication]='" _
& Me![List0].ItemData(i) & "'"
Next i

' Build criteria string from selected items in list box.
Criteria1 = ""
For Each j In Me![List3].ItemsSelected
If Criteria1 <> "" Then
Criteria1 = Criteria1 & " OR "
End If
Criteria1 = Criteria1 & "[Study Drug]='" _
& Me![List3].ItemData(j) & "'"
Next j

'Open report with criteria generated above. The two fields are in an "And" format against the query
stDocName = "rptMedwatchIndication"
DoCmd.OpenReport stDocName, acViewPreview, , Criteria & " AND " & Criteria1

Exit_cmdOpenMedwatchIndication_Click:
Exit Sub

Err_cmdOpenMedwatchIndication_Click:
MsgBox Err.Description
Resume Exit_cmdOpenMedwatchIndication_Click

End Sub
 
Chris said:
I've got a form on which users can select one or more criteria for each field (on the test there are two fields) and click the submit button to generate a report that filters based upon the criteria they've specified. I've got it so that it works if they select one or more of the first field and one of the second field, but if they make multiple selection in the second field it fails to work properly (it seems to generate a multiple "Or" criteria based on all the selections). I'm relatively new to coding so I'm unclear what the issue is with the code. I'm posting the code below for reference if anyone can tell me what is wrong with it, thanks in advance for any/all help: [snip a bunch of good code]
DoCmd.OpenReport stDocName, acViewPreview, , Criteria & " AND " & Criteria1
Click to expand...
[snip the rest of the code]

The problem is that you need to aprcify which terms the And
applies to. This is easily accomplished by adding
parenthesis in startegic places:

. . ., "(" & Criteria & ") AND (" & Criteria1 & ")"

FYI, somtines you can run into a limit on how many terms you
can get into a Where clause. You might want to consider
using the IN operator instead of stringing a bunck or ORs
together.
 
Back
Top