Mutliselect Listbox and Date range

[jenneedshelp]

I recently have ontained some responsibilies from a coworker who has left the company. I have been trying to teach myself (googling and a few books I have borrowed) a lot of this information but I have hit a road block. Please if you are able to help can you please explain your steps so I am actually able to learn.

I have frmWorkReport which uses ratio buttons to populate the
multiselect listbox .. this all works.. now i need to add date ranges and
everything I do seems to screw up what I already have working..

I have added two textboxes - txtStartDate and txtEndDate. Is anyone able to teach me how to do this?

Thank you in advance -


Option Compare
Database

Private Sub ListFilter()

Dim strListSrc As
String

Select Case fraWorkTeam.Value
Case 0
strListSrc = "SELECT
DISTINCT tblWork.Type FROM tblWork WHERE tblWork.[Team] = 'ADVICE' ORDER BY
tblWork.[Type];"
Case 1
strListSrc = "SELECT DISTINCT tblWork.Type FROM
tblWork WHERE tblWork.[Team] = 'HELP' ORDER BY tblWork.[Type];"
Case
2
strListSrc = "SELECT DISTINCT tblWork.Type FROM tblWork WHERE
tblWork.[Team] = 'PAPER' ORDER BY tblWork.[Type];"
Case 3
strListSrc =
"SELECT DISTINCT tblWork.Type FROM tblWork ORDER BY tblWork.[Type];"

End
Select

Me.lstWorkType.RowSource =
strListSrc
Me.lstWorkType.Requery

End Sub

Private Sub
cmdRunReport_Click()

Call SetReport

End Sub


Private Sub
Form_Load()

Me.fraWorkTeam.Value = 3
Call ListFilter

End
Sub

Private Sub fraWorkTeam_AfterUpdate()

Call
ListFilter

End Sub

Private Sub cmdClearAll_Click()

Dim lngX
As Long

With Me.lstWorkType
For lngX = Abs(.ColumnHeads) To
(.ListCount - 1)
.Selected(lngX) = False
Next
End With

End
Sub

Private Sub cmdSelectAll_Click()

Dim lngX As Long

With
Me.lstWorkType
For lngX = Abs(.ColumnHeads) To (.ListCount -
1)
.Selected(lngX) = True
Next
End With

End
Sub


Private Sub SetReport()
On Error GoTo Err_Handler
'Purpose:
Open the report filtered to the items selected in the list box.

Dim
varItem As Variant 'Selected items
Dim strWhere As String 'String to use as
WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim
lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this
field type.
Dim strDoc As String 'Name of report to open.
Dim strFld As
String 'Name of field to query
Dim strSelect As String
Dim qDF As
QueryDef

Set qDF = CurrentDb().QueryDefs("qselWork")

strDelim =
"""" 'Delimiter appropriate to field type. See note 1.

strSelect =
"SELECT tblWork.[Calendar Date], tblWork.Type, tblWork.RECVD, tblWork.HNDL,
tblWork.ABD, tblWork.[% ABD], tblWork.SVL, " & _
"tblWork.ASA,
tblWork.TALK, tblWork.HOLD, tblWork.[Held Calls], tblWork.[Avg Held Call Hold
Time], tblWork.WORK, tblWork.DURATION, " & _
"tblWork.AHT, tblWork.[ANS
<= 30 sec], tblWork.[ABN <= 30 sec], tblWork.LNGST " & _
"FROM
tblWork "


'Loop through the ItemsSelected in the list box.
With
Me.lstWorkType
For Each varItem In .ItemsSelected
If Not IsNull(varItem)
Then
'Build up the filter from the bound column (hidden).
strWhere =
strWhere & strDelim & .ItemData(varItem) & strDelim &
","
'Build up the description from the text in the visible column. See note
2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name,
IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0
Then
strWhere = "tblWork.Type IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip =
"Categories: " & Left$(strDescrip, lngLen)
End If
End
If



Select Case fraWorkTeam.Value

Case 0
strDoc =
strSelect & " WHERE " & strWhere & " AND tblWork.Team =
'ADVICE'"
Case 1
strDoc = strSelect & " WHERE " & strWhere & "
AND tblWork.Team = 'HELP'"
Case 2
strDoc = strSelect & " WHERE " &
strWhere & " AND tblWork.Team = 'PAPER'"
Case 3
strDoc =
strSelect

End Select


qDF.SQL = strDoc


'Report will
not filter if open, so close it.
If SysCmd(acSysCmdGetObjectState, acQuery,
"qselWork") = acObjStateOpen Then
DoCmd.Close acQuery, "qselWork"
End
If

DoCmd.OpenQuery "qselWork"

Exit Sub

Set qDF =
Nothing

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number
<> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " &
Err.Number & " - " & Err.Description, , "OpenReport"
End If
Resume
Exit_Handler
End Sub

 

[jenneedshelp]

This is using Access 2007 - should have included that

 

[jenneedshelp]

I also tried changing this strWhere statement - while following some directions I found online.. but now it just gives me an error -

'Loop through the ItemsSelected in the list box.
With Me.lstWorkType
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "AND tblWork.[Calendar Date]" & Forms!frmWorkReport!txtStartDate & "AND " & Forms!frmWorkReport!txtEndDate
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

 

[TerryHogarth21]

Just to clarify if I'm understanding.

Before you add anything to this, does the output ultimately filter a report based on items in the form's multi-selected list box?

So you are in essence wanting to add to the syntax of a filtering the report a To and From Date (which would be on the same form as the multi-selected list box)?

 

[jenneedshelp]

Hi - thank you for helping.

Yes the multiselect box works. Once I select a ratio box it populates the multiselect lisbox which I would then like to be able to enter dates and have it pull just for that team for those periods of days.

 

[TerryHogarth21]

Hi - thank you for helping.

Yes the multiselect box works. Once I select a ratio box it populates the multiselect lisbox which I would then like to be able to enter dates and have it pull just for that team for those periods of days.

We need to change the select query to only include these dates so on the part that has

 strSelect = 
"SELECT tblWork.[Calendar Date], tblWork.Type, tblWork.RECVD, tblWork.HNDL, 
tblWork.ABD, tblWork.[% ABD], tblWork.SVL, " & _
"tblWork.ASA, 
tblWork.TALK, tblWork.HOLD, tblWork.[Held Calls], tblWork.[Avg Held Call Hold 
Time], tblWork.WORK, tblWork.DURATION, " & _
"tblWork.AHT, tblWork.[ANS 
<= 30 sec], tblWork.[ABN <= 30 sec], tblWork.LNGST " & _
"FROM 
tblWork "

try changing it to this to add the to with a WHERE statement on the dates between your form fields.

strSelect =
"SELECT tblWork.[Calendar Date], tblWork.Type, tblWork.RECVD, tblWork.HNDL,
tblWork.ABD, tblWork.[% ABD], tblWork.SVL, " & _
"tblWork.ASA,
tblWork.TALK, tblWork.HOLD, tblWork.[Held Calls], tblWork.[Avg Held Call Hold
Time], tblWork.WORK, tblWork.DURATION, " & _
"tblWork.AHT, tblWork.[ANS
<= 30 sec], tblWork.[ABN <= 30 sec], tblWork.LNGST " & _
"FROM
tblWork " & “WHERE tbleWork.[Calendar Date] >= “ & Forms![ frmWorkReport]![txtStartDate] & “AND tbleWork.[Calendar Date]<= “ &  Forms![ frmWorkReport]![ txtEndDate] & “;”

Debug.Print (strSelect)

I may have messed up somewhere in the syntax but in the immediate window (Control + G) what does returns after you run through this?

 

[jenneedshelp]

I entered this :

strSelect = "SELECT tblEdge.[Calendar Date], tblEdge.Type, tblEdge.RECVD, tblEdge.HNDL, tblEdge.ABD, tblEdge.[% ABD], tblEdge.SVL, " & _
"tblEdge.ASA, tblEdge.TALK, tblEdge.HOLD, tblEdge.[Held Calls], tblEdge.[Avg Held Call Hold Time], tblEdge.EDGE, tblEdge.DURATION, " & _
"tblEdge.AHT, tblEdge.[ANS <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST " & _
"FROM tblEdge " & "WHERE tblEdge.[Calendar Date] >= " & Forms![frmEdgeReport]![txtStartDate] & "AND tblEdge.[Calendar Date]<= " & Forms![frmEdgeReport]![txtEndDate] & ";"

Debug.Print (strSelect)


and I got "Error 3142 - Characters found after end of SQL statement"

 

[TerryHogarth21]

I entered this :

strSelect = "SELECT tblEdge.[Calendar Date], tblEdge.Type, tblEdge.RECVD, tblEdge.HNDL, tblEdge.ABD, tblEdge.[% ABD], tblEdge.SVL, " & _
"tblEdge.ASA, tblEdge.TALK, tblEdge.HOLD, tblEdge.[Held Calls], tblEdge.[Avg Held Call Hold Time], tblEdge.EDGE, tblEdge.DURATION, " & _
"tblEdge.AHT, tblEdge.[ANS <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST " & _
"FROM tblEdge " & "WHERE tblEdge.[Calendar Date] >= " & Forms![frmEdgeReport]![txtStartDate] & "AND tblEdge.[Calendar Date]<= " & Forms![frmEdgeReport]![txtEndDate] & ";"

Debug.Print (strSelect)


and I got "Error 3142 - Characters found after end of SQL statement"

take out this part on last line

 " & "

right between the tblEdge and WHERE

If you press control +G when you are debugging in the VBE editor (Alt + F11) what does the strSelect syntax have?

 

[jenneedshelp]

SELECT tblWork.[Calendar Date], tblWork.Type, tblWork.RECVD, tblWork.HNDL, tblWork.ABD, tblWork.[% ABD], tblWork.SVL, tblWork.ASA, tblWork.TALK, tblWork.HOLD, tblWork.[Held Calls], tblWork.[Avg Held Call Hold Time], tblWork.WORK, tblWork.DURATION, tblWork.AHT, tblWork.[ANS <= 30 sec], tblWork.[ABN <= 30 sec], tblWork.LNGST FROM tblWork WHERE tblWork.[Calendar Date] >= 8/13/2014AND tblWork.[Calendar Date]<= 8/16/2014;

 

[TerryHogarth21]

Try this now and see if you get any errors - I missed an extra space after the date on the startdate text on the form and the # for the dates.

strSelect = "SELECT tblEdge.[Calendar Date], tblEdge.Type,  tblEdge.RECVD, tblEdge.HNDL, tblEdge.ABD, tblEdge.[% ABD], tblEdge.SVL, "  & _
    "tblEdge.ASA, tblEdge.TALK, tblEdge.HOLD, tblEdge.[Held Calls],  tblEdge.[Avg Held Call Hold Time], tblEdge.EDGE, tblEdge.DURATION, "  & _
    "tblEdge.AHT, tblEdge.[ANS <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST " & _
    "FROM tblEdge WHERE tblEdge.[Calendar Date] >= #" &  Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar  Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"