date range and listbox - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

date range and listbox

Author

Topic

jenneeanne
Starting Member

3 Posts

Posted - 2014-11-20 : 15:43:06

Good afternoon all -

I recently have assumed 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

jenneeanne
Starting Member

3 Posts

Posted - 2014-11-20 : 15:52:10

This is in Access 2007 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources