How to String IDs from Multi-Select List Box with Date Range andOther Criteria

[6afraidbecause789]

If able, can someone please help make a Where clause that strings
together IDs in a multi-select listbox AND includes a date range.

I wasn’t thinking when I used the code below that strings together the
IDs of Clients from a multi-select listbox in an unbound text field,
txtCriteria, on a form that is used to pick different reports. It
appears that I now have so many clients that I’ve reached the 255
character limit in the txtCriteria field, thus leaving some clients
out of the report filter—so I need to use the Open Args instead?

I’m trying to piece together info from Allen Brown, from his pages
http://allenbrowne.com/ser-62.html, http://allenbrowne.com/ser-50.html,
and http://www.allenbrowne.com/casu-08.html.

My initial, adapted code worked great, accept for the fact that
clients at the end of the list weren’t being included in the where.
These are the fields in the report selection form:

lstChosen = multi-select listbox with Client IDs and names in columns
txtCriteria = temp textbox to string the selected Clients – this needs
to be omitted an just put into a where
optNames = option group to toggle between client full and abbreviated
names for HIPAA privacy reasons

********INITIAL CODE********

Private Sub cmdViewDailySum mary_Click()

On Error GoTo Err_cmdViewDail ySummary_Click

Dim strDoc As String
Dim varItem As Variant

Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "txtDatePar t"

Me.txtCriteria = ""

For Each varItem In lstChosen.Items Selected
Me.txtCriteria = Me.txtCriteria & "," &
lstChosen.ItemD ata(varItem)
Next varItem

Me.txtCriteria = Mid(Me.txtCrite ria, 2)

If Me.optNames = 1 Then
strDoc = "rptSummaryToda yAscFull"
If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then
strWhere = strField & " <= " & Format(Me.txtEn dDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then
strWhere = strField & " >= " & Format(Me.txtSt artDate,
conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtSt artDate, conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End If

DoCmd.OpenRepor t strDoc, acPreview, , strWhere
ElseIf Me.optNames = 2 Then
strDoc = "rptSummaryToda yAsc"

If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then
strWhere = strField & " <= " & Format(Me.txtEn dDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then
strWhere = strField & " >= " & Format(Me.txtSt artDate,
conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtSt artDate, conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End If
DoCmd.OpenRepor t strDoc, acPreview, , strWhere
End If

Exit_cmdViewDai lySummary_Click :
Exit Sub

Err_cmdViewDail ySummary_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdViewDai lySummary_Click
End If

End Sub

********ATTEMPT TO REVISE********

Private Sub cmdViewDailySum mary_Click()

On Error GoTo Err_cmdViewDail ySummary_Click

On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

Dim strField As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "txtDatePar t"

With Me.lstChosen
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varIt em) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ClientID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Clients: " & Left$(strDescri p, lngLen)
End If
End If

‘ This is where the above where statement containing Clients need to
now contain the date range.

If Me.optNames = 1 Then
strDoc = "rptSummaryFull "
DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

ElseIf Me.optNames = 2 Then
strDoc = " rptSummaryShort "
DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

End If

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdViewDailySu mmary_Click"
End If
Resume Exit_Handler
So, ultimately, a string can be passed into one or more textboxes in
the proper report in this format: =[rptSummary].[OpenArgs].

Without this group and sites like Allen, I’d be even more lost.
Thanks for any and all help.

[Bob Quintal]

Hi,
there's no reason to be afraid.

Instead of putting the concatenated list into your textbox, just put
them to a string variable.

See the changes I've made in the Initial code as an example. No need
for openargs.

6a************* **@gmail.com wrote in
news:20******** *************** ***********@t54 g2000hsg.google groups.co
m:

If able, can someone please help make a Where clause that strings
together IDs in a multi-select listbox AND includes a date range.

I wasn’t thinking when I used the code below that strings together
the IDs of Clients from a multi-select listbox in an unbound text
field, txtCriteria, on a form that is used to pick different
reports. It appears that I now have so many clients that I’ve
reached the 255 character limit in the txtCriteria field, thus
leaving some clients out of the report filter—so I need to use the
Open Args instead?

I’m trying to piece together info from Allen Brown, from his pages
http://allenbrowne.com/ser-62.html,
http://allenbrowne.com/ser-50.html, and
http://www.allenbrowne.com/casu-08.html.

My initial, adapted code worked great, accept for the fact that
clients at the end of the list weren’t being included in the
where. These are the fields in the report selection form:

lstChosen = multi-select listbox with Client IDs and names in
columns txtCriteria = temp textbox to string the selected Clients
– this needs to be omitted an just put into a where
optNames = option group to toggle between client full and
abbreviated names for HIPAA privacy reasons

********INITIAL CODE********

Private Sub cmdViewDailySum mary_Click()

On Error GoTo Err_cmdViewDail ySummary_Click

Dim strDoc As String
Dim varItem As Variant

Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strField = "txtDatePar t"

Me.txtCriteria = "" ' delete this

Dim strClientList as string
Dim strAnd as string

>
For Each varItem In lstChosen.Items Selected

' some changes here.
strClientList = strClientList & "," &
lstChosen.ItemD ata(varItem)
Next varItem

strClientList = Mid(strClientLi st, 2)
if len(strclientLi st) 0 then
strClientlist "ClientID IN (" & strClientID & ")"
end if

' I've split the report selection code from the date filter
' because the date filter was duplicated.

If Me.optNames = 1 Then
strDoc = "rptSummaryToda yAscFull"
ElseIf Me.optNames = 2 Then
strDoc = "rptSummaryToda yAsc"
End If

If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then
strWhere = strField & " <= " _
& Format(Me.txtEn dDate, conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then
strWhere = strField & " >= " _
& Format(Me.txtSt artDate, conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtSt artDate, conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End IF

' The big change is here.
' We test if the date filter is empty and the client filter is empty.
' If neither is empty, we need an And between them, othrewise we need
nothing.
If len(strWhere) 0 AND len(strClientLi st) >0 then
strAnd = " AND "
end if

strwhere = strwhere & strAnd & strClientList
DoCmd.OpenRepor t strDoc, acPreview, , strWhere

Exit_cmdViewDai lySummary_Click :
Exit Sub

Err_cmdViewDail ySummary_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdViewDai lySummary_Click
End If

End Sub

So, ultimately, a string can be passed into one or more textboxes
in the proper report in this format: =[rptSummary].[OpenArgs].

Without this group and sites like Allen, I’d be even more lost.
Thanks for any and all help.

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[Rich P]

It sounds like you want to get a subset of data for the recordsource of
a report where this subset of data is based on a list of clients.

If this is the case then it sounds like you are concatenating this list
of clients as one string to be used as criteria. If this is the case
then what you want to do is to copy the list of clients to a temp table
and use something like the following for the recordsource of your
reports

rpt.RecordSourc e = "Select distinct * from someTbl Where ClientName In
(Select * From tmpClient)"

using sql can shrink your coding from hundreds of lines to 1 or 2 lines
of code.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[6afraidbecause789]

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU