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 frmEdgeReport 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?

Currently I am getting error when i put the SELECT statement in its own query that says "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."

and when I just run my form I am getting an errror that says "Error 3142 - Characters found after end of SQL statement"

Thank you in advance -

this is what i have so far :

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.WORK, 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 = 'Team1'"
Case 1
strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team2'"
Case 2
strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team3'"
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

[ssanfu]

I have frmEdgeReport 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?

Not clear what you are asking. Would have to see the dB.

Currently I am getting error when i put the SELECT statement in its own query that says "Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'."

This error usually means that you tried to open a SELECT query using "Docmd.RunSQL" or "Currentdb.Execute".
"Docmd.RunSQL" or "Currentdb.Execute" can only be used for action queries. (ie 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE' queries)

and when I just run my form I am getting an errror that says "Error 3142 - Characters found after end of SQL statement"

This one is easy.
The semi-colon ( ; ) is the line terminator in SQL.
When you create the SQL for the variable "strSelect", there is a semi-colon after the hash (date delimiter).

WHERE tblEdge.[Calendar Date] >= #" & Forms![frmEdgeReport]![txtStartDate] & "# AND tblEdge.[Calendar Date]<= #" & Forms![frmEdgeReport]![txtEndDate] & "#;"

Then, lower in the code you have

    Select Case fraEdgeTeam.value
        Case 0
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team1'"
        Case 1
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team2'"
<snip>

Here you are concantation the string "strSelect" (which has the semi-colon terminator) with the string "WHERE", the string "strWHERE" and more text.

So there are two issues here: the SQL terminator ( ; ) and multiple "WHERE" clauses for Cases 0 - 2.
If you put a "Debug.Print" statement after the "End Select" statement, you will see the semi-colon terminator and the multiple "WHERE" statements.