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]

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] & "#;"

I'm still getting "Error 3142 - Characters found after end of SQL statement"

Is it possible there is an issue somewhere else that im missing?

Here is my whole code just incase

Option Compare Database

Private Sub ListFilter()

Dim strListSrc As String

Select Case fraEdgeTeam.Value
Case 0
strListSrc = "SELECT DISTINCT tblEdge.Type FROM tblEdge WHERE tblEdge.[Team] = 'FAC' ORDER BY tblEdge.[Type];"
Case 1
strListSrc = "SELECT DISTINCT tblEdge.Type FROM tblEdge WHERE tblEdge.[Team] = 'MLD' ORDER BY tblEdge.[Type];"
Case 2
strListSrc = "SELECT DISTINCT tblEdge.Type FROM tblEdge WHERE tblEdge.[Team] = 'VCA' ORDER BY tblEdge.[Type];"
Case 3
strListSrc = "SELECT DISTINCT tblEdge.Type FROM tblEdge ORDER BY tblEdge.[Type];"

End Select

Me.lstEdgeType.RowSource = strListSrc
Me.lstEdgeType.Requery

End Sub

Private Sub cmdRunReport_Click()

Call SetReport

End Sub


Private Sub Form_Load()

Me.fraEdgeTeam.Value = 3
Call ListFilter

End Sub

Private Sub fraEdgeTeam_AfterUpdate()

Call ListFilter

End Sub

Private Sub cmdClearAll_Click()

Dim lngX As Long

With Me.lstEdgeType
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.lstEdgeType
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("qselEdge")

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

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)



'Loop through the ItemsSelected in the list box.
With Me.lstEdgeType
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 = "tblEdge.Type IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If



Select Case fraEdgeTeam.Value

Case 0
strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'FAC'"
Case 1
strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'MLD'"
Case 2
strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'VCA'"
Case 3
strDoc = strSelect

End Select


qDF.SQL = strDoc


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

DoCmd.OpenQuery "qselEdge"

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

 

[TerryHogarth21]

Usually this error occurs after some syntax occurs after the end of a sql statement with words after semicolon - this will have to be reviewed but can you do me favor first to ensure the query runs with the expected results?

I do need you to get the strSelect result that is displayed on the immediate window (Control +G)
then paste that into a query (create a query and right-click view SQL) and run that query (you may need to have that form open with the to/from dates).

Just want to isolate the instance first to ensure this query works or if it is the cause of the error.

 

[jenneedshelp]

Usually this error occurs after some syntax occurs after the end of a sql statement with words after semicolon - this will have to be reviewed but can you do me favor first to ensure the query runs with the expected results?

I do need you to get the strSelect result that is displayed on the immediate window (Control +G)
then paste that into a query (create a query and right-click view SQL) and run that query (you may need to have that form open with the to/from dates).

Just want to isolate the instance first to ensure this query works or if it is the cause of the error.

Terry -

Thanks again for your help.

When I do Ctrl G I get

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.WORK, tblEdge.DURATION, tblEdge.AHT, tblEdge.[ANS <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST FROM tblEdge WHERE tblEdge.[Calendar Date] >= #9/1/2014# AND tblEdge.[Calendar Date]<= #9/6/2014#;


Before we added to the strSelect statement .. "WHERE tblEdge.[Calendar Date] >= #" & Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"" everything ran as it should .. minus it filtering by dates. It would filter the listbox by type and then I could choose teams and then the output would only be for those teams.

Not sure if this is what you needed to know. I really do appreciate your help.

 

[TerryHogarth21]

I just need you to now paste this

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.WORK, tblEdge.DURATION, tblEdge.AHT, tblEdge.[ANS  <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST FROM tblEdge  WHERE tblEdge.[Calendar Date] >= #9/1/2014# AND tblEdge.[Calendar Date]<= #9/6/2014#;

Into it's own query and let me know if it filters all that data between those dates.

 

[jenneedshelp]

I just need you to now paste this

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.WORK, tblEdge.DURATION, tblEdge.AHT, tblEdge.[ANS  <= 30 sec], tblEdge.[ABN <= 30 sec], tblEdge.LNGST FROM tblEdge  WHERE tblEdge.[Calendar Date] >= #9/1/2014# AND tblEdge.[Calendar Date]<= #9/6/2014#;

Into it's own query and let me know if it filters all that data between those dates.

Okay so when enter it in a query it asked me to Enter Parameter Value "tblEdge.Avg Held Call Hold Time" but when I went back to design view it did have >=#9/1/2014# And <=#9/6/2014# in the criteria.. so it is pulling the dates..

 

[TerryHogarth21]

Okay so when enter it in a query it asked me to Enter Parameter Value "tblEdge.Avg Held Call Hold Time" but when I went back to design view it did have >=#9/1/2014# And <=#9/6/2014# in the criteria.. so it is pulling the dates..

Is syntax correct on the field [Avg held Call Hold Time]? Does that field exist in the table tblEdge and is there already data in there?

Unfortunately the field names don't follow Database reference of not using spaces but to use underscores or dashes instead that's why it's difficult to look at it in thread view for me.

See if there is an extra space in there somewhere - or just copy/paste the field name between the [] when you look at the table.

 

[TerryHogarth21]

Is syntax correct on the field [Avg held Call Hold Time]? Does that field exist in the table tblEdge and is there already data in there?

Unfortunately the field names don't follow Database reference of not using spaces but to use underscores or dashes instead that's why it's difficult to look at it in thread view for me.

See if there is an extra space in there somewhere - or just copy/paste the field name between the [] when you look at the table.

OK I figured what's going on that's causing that error.

It's doubling up on the WHERE clause since the Case statement combines the strSelect and adds it's own WHERE clause below.

Select Case fraEdgeTeam.Value
       
        Case 0
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'FAC'"
        Case 1
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'MLD'"
        Case 2
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'VCA'"
        Case 3
            strDoc = strSelect
           
        End Select

So remove the WHERE clause that I gave you in the StrSelect and you could add it after each of the select case like below. (or you can declare it as another string variable in the Private Sub SetReport() - whichever is easier for you.

example below

 Select Case fraEdgeTeam.Value
       
        Case 0
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'FAC' AND tblEdge.[Calendar Date] >= #" &  Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar  Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"         
        Case 1
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'MLD' AND tblEdge.[Calendar Date] >= #" &   Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar   Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"
        Case 2
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'VCA' AND tblEdge.[Calendar Date] >= #" &   Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar   Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"
        Case 3
            strDoc = strSelect
           
        End Select

 

[jenneedshelp]

Is syntax correct on the field [Avg held Call Hold Time]? Does that field exist in the table tblEdge and is there already data in there?

Unfortunately the field names don't follow Database reference of not using spaces but to use underscores or dashes instead that's why it's difficult to look at it in thread view for me.

See if there is an extra space in there somewhere - or just copy/paste the field name between the [] when you look at the table.

I did find an extra space.. now i am getting this message when i post it in the query

"Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'.

 

[TerryHogarth21]

I did find an extra space.. now i am getting this message when i post it in the query

"Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'.

If you took off the WHERE clause and run it does it work?

What's the actual date format look like in that field [Calendar Date]?

 

[jenneedshelp]

If you took off the WHERE clause and run it does it work?

What's the actual date format look like in that field [Calendar Date]?

sorry this is such a pain Terry

without the WHERE statement it does run.
the format of the date is a short date - ex. 12/31/2012