By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 cmdViewDailySummary_Click()

On Error GoTo Err_cmdViewDailySummary_Click

Dim strDoc As String
Dim varItem As Variant

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

Me.txtCriteria = ""

For Each varItem In lstChosen.ItemsSelected
Me.txtCriteria = Me.txtCriteria & "," &
lstChosen.ItemData(varItem)
Next varItem

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

If Me.optNames = 1 Then
strDoc = "rptSummaryTodayAscFull"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

DoCmd.OpenReport strDoc, acPreview, , strWhere
ElseIf Me.optNames = 2 Then
strDoc = "rptSummaryTodayAsc"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
DoCmd.OpenReport strDoc, acPreview, , strWhere
End If

Exit_cmdViewDailySummary_Click:
Exit Sub

Err_cmdViewDailySummary_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdViewDailySummary_Click
End If

End Sub

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

Private Sub cmdViewDailySummary_Click()

On Error GoTo Err_cmdViewDailySummary_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 = "txtDatePart"

With Me.lstChosen
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
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$(strDescrip, 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.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

ElseIf Me.optNames = 2 Then
strDoc = " rptSummaryShort"
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

End If

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdViewDailySummary_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.
Aug 20 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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**********************************@t54g2000 hsg.googlegroups.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 cmdViewDailySummary_Click()

On Error GoTo Err_cmdViewDailySummary_Click

Dim strDoc As String
Dim varItem As Variant

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

Me.txtCriteria = "" ' delete this
Dim strClientList as string
Dim strAnd as string
>
For Each varItem In lstChosen.ItemsSelected
' some changes here.
strClientList = strClientList & "," &
lstChosen.ItemData(varItem)
Next varItem

strClientList = Mid(strClientList, 2)
if len(strclientList) 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 = "rptSummaryTodayAscFull"
ElseIf Me.optNames = 2 Then
strDoc = "rptSummaryTodayAsc"
End If

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " _
& Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " _
& Format(Me.txtStartDate, conDateFormat)
Else
strWhere = strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, 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(strClientList) >0 then
strAnd = " AND "
end if

strwhere = strwhere & strAnd & strClientList
DoCmd.OpenReport strDoc, acPreview, , strWhere
Exit_cmdViewDailySummary_Click:
Exit Sub

Err_cmdViewDailySummary_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdViewDailySummary_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 **
Aug 20 '08 #2

P: n/a
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.RecordSource = "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 ***
Aug 20 '08 #3

P: n/a
THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU
Aug 21 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.