SQL - Search criteria using multiple list boxes & strings

[Andyxx]

Hi all, I am an amateur access developer mostly trained by Google searches & YouTube, so please be gentle with me

Background - I built a database for my department that stores 100's of clinical trials, so we can keep track of where everything is up to.

I have created a search page that has multiple criteria using combo boxes, list boxes and free text. I've attached the full SQL code for reference.

At the moment, when I select a Specialty using the list box, the code runs through the list box criteria selected, adds this to the variable strSpec, then adds the combined specialties into the strWhere and continues with the rest of the search criteria.

Help Request -
Some trials cover multiple specialties. I have another field called MulipleSpecialty and when the user creates or updates a record, they can select Multiple in the Specialty field then use a multi seclect combobox to store all the related specialties in a string in MultipleSpecialty.

I'd like to be able to check if the Specialty field = "Multiple", then search through the MultipleSpecialty field to compare against what has been selected in the list box, lst_spec
(as a side note, the string in MultipleSpecialty is a number field, and stores the IDs of the specialties selected from tbl.Specialties)

Here is an exert from the code building the Specialty criteria.

'SPECIALTY CRITERIA

'Build the specialty criteria string by looping through the listbox
For i = 0 To lst_spec.ListCount - 1
If lst_spec.Selected(i) Then
strSpec = strSpec & "'" & lst_spec.Column(0, i) & "',"
End If
Next i

' strip off the last comma
If Len(strSpec) > 0 Then
strSpec = Left(strSpec, Len(strSpec) - 1)
End If

' if nothing has been selected then don't add to the strwhere
If Len(strSpec) > 0 Then
strWhere = strWhere & "[Specialty] in (" & strSpec & ") AND "
End If

Thanks in advance for any help or advice,

Andy M

 

[arnelgp]

i thought your multi-select combo field is MultipleSpecialty? why is it not included in your where clause.

also since this field is numeric you don't add Delimiter to the criteria.

For i = 0 To lst_spec.ListCount - 1
If lst_spec.Selected(i) Then
strSpec = strSpec & lst_spec.Column(0, i) & ","
End If
Next i

since the field is Multi-select Value you add Value to your criteria:

..
..
   ' if nothing has been selected then don't add to the strwhere
    If Len(strSpec) > 0 Then
    strWhere = strWhere & "[MultipleSpecialty].[Value] in (" & strSpec & ") AND "
        End If
..
..

 

[isladogs]

See whether these example apps are any help in constructing the search SQL Multiple Group & Filter - Mendip Data Systems

 

[Andyxx]

i thought your multi-select combo field is MultipleSpecialty? why is it not included in your where clause.

also since this field is numeric you don't add Delimiter to the criteria.

For i = 0 To lst_spec.ListCount - 1
If lst_spec.Selected(i) Then
strSpec = strSpec & lst_spec.Column(0, i) & ","
End If
Next i

since the field is Multi-select Value you add Value to your criteria:

..
..
   ' if nothing has been selected then don't add to the strwhere
    If Len(strSpec) > 0 Then
    strWhere = strWhere & "[MultipleSpecialty].[Value] in (" & strSpec & ") AND "
        End If
..
..

Thanks for your input. Maybe I didn't explain fully what I wanted.

lst_spec is the list box on the search page, where you select one or more Specialties to add to the search criteria.
tblOTR[Specilaty] is where a single specialty is stored for each record
tblSpecialty lists all the specialties plus the text Multiple and ALL in the table too.
.
If the trial covers multiple specialties, I can mark the Specialty field as 'Multiple' then I have another field called tblOTR[MultipleSpecialty] to store all the specialty IDs linked to that trial.

So the code I posted originally takes the selected specialties in lst_spec and checks against tblOTR[Specialty].
What I need to add is if the [Specialty] = Multiple, then check lst_spec against tblOTR[MultipleSpecialty] instead.

Hope that makes sense.

Also, if it helps simplify the code, I will look to convert [MultipleSpecialty] into text and store the Specialty names instead of IDs. I think this is partially the reason I'm struggling with this.

Thanks again,

Andy M

 

[arnelgp]

I am confused as to why you made to fields (specialty, multiplespecialty) when you can
compress all in specialty field?

after changing to textfield your MultipleSpecialty field,
you can therefore check Both fields if one OR the other has the value specified in your strSpec criteria:

..
..
   ' if nothing has been selected then don't add to the strwhere
    If Len(strSpec) > 0 Then
    strWhere = strWhere & "([Specialty] In (" & strSpec & ") OR [MultipleSpecialty].[Value] In (" & strSpec & ")) AND "
        End If
..
..