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

 

[TerryHogarth21]

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

What about if you use this query?

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

change the dates if need be.

If that gives you an error maybe your Field data type is really text (look in design mode) instead of Date/Time.

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= '11/1/2014' AND tbleEDGE.[Calendar Date] <= '11/5/2014';

change the dates if need be.

By chance do you have any records with Null dates in that [Calendar Date] field?

 

[jenneedshelp]

What about if you use this query?

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

change the dates if need be.

If that gives you an error maybe your Field data type is really text (look in design mode) instead of Date/Time.

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= '11/1/2014' AND tbleEDGE.[Calendar Date] <= '11/5/2014';

change the dates if need be.

By chance do you have any records with Null dates in that [Calendar Date] field?

Every entry has a date therefore there should be no Null dates. I also double checked and the Calendar Date field is in Date/Time format.

When I entered just :

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

it asked me to enter tblEdge.Calendar Date - I entered 11/1/2014 just to see what would happen and then it just gave me all the enteries in the tbl.
If we ever get this figured out hopefully you can explain what I missed so I can make sure I can do it on my own next time.

 

[TerryHogarth21]

Every entry has a date therefore there should be no Null dates. I also double checked and the Calendar Date field is in Date/Time format.

When I entered just :

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

it asked me to enter tblEdge.Calendar Date - I entered 11/1/2014 just to see what would happen and then it just gave me all the enteries in the tbl.
If we ever get this figured out hopefully you can explain what I missed so I can make sure I can do it on my own next time.

Did you ensure that you have [] and the Calendar Date in between? You'll need that since there's spaces on the field name.

I plugged this below into a dummy database I have and it brings me back the expected results.

SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

</pre>

 

[jenneedshelp]

Terry - is it possible to put my datebase any where on here?

 

[jenneedshelp]

Terry -

I posted on another board to try and get another set of eyes on this and I got :

"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] & "#[B][COLOR=#ff0000];[/COLOR][/B]"/CODE]
"Then, lower in the code you have"
[CODE]    Select Case fraEdgeTeam.value
        Case 0
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team1'"
        Case 1
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team2'"

"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."


My head is spinning- I'm not sure what to do here...

 

[TerryHogarth21]

Terry -

I posted on another board to try and get another set of eyes on this and I got :

"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] & "#[B][COLOR=#ff0000];[/COLOR][/B]"/CODE]
"Then, lower in the code you have"
[CODE]    Select Case fraEdgeTeam.value
        Case 0
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team1'"
        Case 1
            strDoc = strSelect & " WHERE " & strWhere & " AND tblEdge.Team = 'Team2'"

"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."


My head is spinning- I'm not sure what to do here...

Yes i had mentioned of that above in the double up on the WHERE clause and StrSelect syntax.

The issue is still relating to your date format in the [Calendar Field].

You want to try and get the select query to work before we can continue on with building it based on form data.

 SELECT * FROM tblEDGE WHERE tbleEDGE.[Calendar Date] >= #11/1/2014# AND tbleEDGE.[Calendar Date] <= #11/5/2014#;

Is there a time stamp or anything in the [Calendar Date] field?

Can you paste just 1 record (or create some dummy record) to see what it looks like?


</pre>

 

[jenneedshelp]

ID	Fiscal Year	Fiscal Year - Quarter	Fiscal Year - Month	Fiscal Year - Week	Calendar Date	Day of Week	Team	Type	RECVD	HNDL	ABD	% ABD	SVL	ASA	TALK	HOLD	Held Calls	Avg Held Call Hold Time	WORK	DURATION	AHT	ANS <= 30 sec	ABN <= 30 sec	LNGST
1	2013	2013 Q1	2013/01	2013 Week 01	01/03/2013	Thursday	FAC	R F	0	0	0	0	0.0%	0	0	0	0	0	0	0	0	0	0	0

<caption>tblEdge1</caption>

<thead>


</thead>
<tbody>


</tbody>
<tfoot></tfoot>

 

[TerryHogarth21]

I imported this into a database and used this as a query, try it and let me know if this works for you. (change the dates if you want).

 SELECT tblEDGE.*
FROM tblEDGE
WHERE (((tblEDGE.[Calendar Date])>=#1/1/2013# And (tblEDGE.[Calendar Date])<=#1/13/2013#));

 

[jenneedshelp]

i imported this into a database and used this as a query, try it and let me know if this works for you. (change the dates if you want).

 select tbledge.*
from tbledge
where (((tbledge.[calendar date])>=#1/1/2013# and (tbledge.[calendar date])<=#1/13/2013#));

it worked!

 

[TerryHogarth21]

Great so now you can finish it off by declaring a new variable.

 Dim strDate as string

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

'Now in the Case Statement add the new string to the end

 Select Case fraEdgeTeam.Value

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

End Select