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]

I entered it like this and got "Compile error : Expected expression"

 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 "
    
    strDate = “ AND (((tblEdge.[calendar date])>=#” & Forms![frmEdgeReport]![txtStartDate] & “# AND (tblEdge.[calendar date])<=#” & Forms![frmEdgeReport]![txtEndDate] & “#));”

this is making sense though- we are setting up two parameters for it to search by.

 

[TerryHogarth21]

Could you change the case statement to the below, run it, then see what comes out in the immediate window? (Control + G)

 Select Case fraEdgeTeam.Value  

Case 0 strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'FAC'” & strDate
debug.print strDoc 

Case 1 strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'MLD'" & strDate
debug.print strDoc 

Case 2 strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'VCA'" & strDate
debug.print strDoc 

Case 3 strDoc = strSelect
debug.print strDoc  

End Select

 

[jenneedshelp]

its not even getting that far -

I'm getting the error (and its red text) at :

 strDate = “ AND (((tblEdge.[calendar date])>= #” & Forms![frmEdgeReport]![txtStartDate] & “# AND (tblEdge.[calendar date])<=#” & Forms![frmEdgeReport]![txtEndDate] & “#));”

 

[TerryHogarth21]

its not even getting that far -

I'm getting the error (and its red text) at :

 strDate = “ AND (((tblEdge.[calendar date])>= #” & Forms![frmEdgeReport]![txtStartDate] & “# AND (tblEdge.[calendar date])<=#” & Forms![frmEdgeReport]![txtEndDate] & “#));”

It's running into the same problem as passing parameters from a multiselect listbox into a query.
Microsoft Access tips: Use a multi-select list box to filter a report

What does this actual query do?

qselEdge

here's what I ended up doing to get it to work like how I'm thinking you want it.

1) I created a query called qselEdge this is what is in it.

 SELECT TblEdge.*
FROM TblEdge
WHERE (((TblEdge.[Calendar Date]) Between [Forms]![frmEdgeReport]![txtStartDate] And [Forms]![frmEdgeReport]![txtEndDate]));

2) I created a report called tblEdgeQueryReport based on the above query.

3) In the Form frmEdgeReport is where the multi select list box values and the start and end dates exist
a) There is a button on this form called Buttonz with the below in the On Click event.

 Private Sub Buttonz_Click()

'Declare variable
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
Dim sSQL As String
Dim strDoc As String

'Name of Report
strDoc = "tblEdgeQueryReport"

'make sure a selection has been made
If Me.TeamSelection.ItemsSelected.Count = 0 Then
  MsgBox "You must pick an item"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.TeamSelection
For Each varItem In ctl.ItemsSelected

  'strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
Debug.Print strWhere


'open the report, restricted to the selected items
 DoCmd.OpenReport strDoc, acPreview, , "Team IN(" & strWhere & ")"

End Sub

What happens is the query in Access already filters those items between the date in the report, then subsequently your multi selected list box of the items that you picked, will filter the report based on those teams that were selected. That's the way to bypass the Multi-Selected Listbox Form dilemma and utilizing it to create a sql string query in VBA.

So based on this working you need to leave the sql str that creates the SELECT Query out of VBA and put that into Access as a query itself.
The key will be what does the query qselEdge do? We need to tweak this to become the select query that has only the data with those dates on the form, then utilize multi-selected listbox to fitler the report based on the team names.

I should have remember this to save myself some time that i answered a few weeks back but it must've been all that turkey I ate last week.
http://www.mrexcel.com/forum/microsoft-access/817830-query-criteria-list-box.html