Multi-Select filter for Concatenated field

[Vittles]

I have a form with a multi-select list box that I use to filter several reports. If nothing is selected and the button for a report is clicked, all of the data is given, otherwise, if one or more people's names are selected only the data related to the selected individuals is shown.

So far I have 2 reports set up which are working great using this form. I went to add another report, but haven't had the same results. This report is based on a query 'PubMed Info' and has a concatenated text box field on it. When I select a person from the list the report's openargs and filter is set correctly, but the concatenated field does not get filtered. The formula for this field is ="{Insert Reviewer Last Name First Initial[AU] } AND (2003 [DP] OR 2004 [DP] OR 2005 [DP] OR 2006 [DP] OR 2007[DP]) AND (" & ConcatenateOr("Select[Last Name] & ' ' & [First Initial] & '[AU]' from [PubMed Info]") & ")"

I think the concatenation is still pulling from the original query - not the filtered version. Is there a way to have this report & it's field function like the other reports which use this multi-select list?

ps. I haven't found a successful way to incorporate the field into the baseline query itself as it becomes too complex.

Thanks for any responses.

 

[PHV]

Which language did you use for The formula for this field is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Vittles]

Thanks for responding. I am not sure I know what you are asking...but here are more specifics. I am using Access 2003.

I have tried putting the control source of:

="{Insert Reviewer Last Name First Initial[AU] } AND (2003 [DP] OR 2004 [DP] OR 2005 [DP] OR 2006 [DP] OR 2007[DP]) AND (" & ConcatenateOr("Select[Last Name] & ' ' & [First Initial] & '[AU]' from [PubMed Info]") & ")"

into the text box itself as well as trying to assign this value via the on print & on format events by adding:
me.Results="{Insert Reviewer Last Name First Initial[AU] } AND (2003 [DP] OR 2004 [DP] OR 2005 [DP] OR 2006 [DP] OR 2007[DP]) AND (" & ConcatenateOr("Select[Last Name] & ' ' & [First Initial] & '[AU]' from [PubMed Info]") & ")"

The ConcatenateOr command calls the following module into play (I adjusted the original to include an 'or' between each value):

Function ConcatenateOr(pstrSQL As String, _
Optional pstrDelim As String = " Or ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
ConcatenateOr = strConcat
End Function

Does this help?