I have attempted to filter by date using a query, VBA, and in the Property Sheet's Row Source directly to no avail. The following is a screenshot of what requires filtering just for situational awareness.
The following code reports an error when used in a query, which is to be expected due to unrecognized date fields. It also reports an error of trying to request too much information but that was an issue elsewhere as well and was resolved with adjustment to syntax. The same code used in Property Sheet's Row Source without success or error reporting.
TRANSFORM Count(CaseTrend.CaseNum) AS CaseCount
SELECT CaseTrend.MonthAbbr
FROM (SELECT Month([DateCreated]) AS ID, Format([DateCreated], "mmm") AS MonthAbbr, Year([DateCreated]) AS Yr, CaseNum FROM tblCase UNION SELECT DISTINCT tblLookupMonth.ID, MonthAbbr, Year([DateCreated]) AS Yr, Null FROM tblLookupMonth, tblCase) AS CaseTrend
WHERE (((CaseTrend.ID) Between [Forms]![frmViewStats]![txtBeginDateFilterCase] And [Forms]![frmViewStats]![txtEndDateFilterCase]))
GROUP BY CaseTrend.ID, CaseTrend.MonthAbbr
PIVOT CaseTrend.Yr;
The following was attempted in VBA; however, may have an error in the order of filtering. The strSQL is continuous line and not placed on new line as depicted in the following example. There are two date text box shown in red with a button that calls the chtChaseTrend_GotFocus() when it is clicked. The following still shows the entire chart based on the query but does not perform any filtering whatsoever. I have attempted reordering and adjusting numerous times without avail. The first part of the "IF" simply displays the chart if no date range is entered and the "ELSE" filters by the date range. So essentially the "ELSE" portion is what I am interested in resolving here as the remainder functions as intended.
Private Sub chtCaseTrend_GotFocus()
Dim strSQL As String
Dim strFiltered As String
Dim strFilter As String
If [Forms]![frmViewStats]![txtBeginDateFilterCase] And [Forms]![frmViewStats]![txtEndDateFilterCase] = "" Then
strSQL = "TRANSFORM Count(CaseTrend.CaseNum) AS CaseCount SELECT CaseTrend.MonthAbbr FROM (SELECT Month([DateCreated]) AS ID, Format([DateCreated], 'mmm') AS MonthAbbr, Year([DateCreated]) AS Yr, CaseNum FROM tblCase UNION SELECT DISTINCT tblLookupMonth.ID, MonthAbbr, Year([DateCreated]) AS Yr, Null FROM tblLookupMonth, tblCase) AS CaseTrend GROUP BY CaseTrend.ID, CaseTrend.MonthAbbr PIVOT CaseTrend.Yr "
With Me![chtCaseTrend]
.RowSource = strSQL
.RowSourceType = "Table/Query"
.Enabled = True
.Requery
End With
Else
strSQL = "TRANSFORM Count(CaseTrend.CaseNum) AS CaseCount SELECT CaseTrend.MonthAbbr FROM (SELECT Month([DateCreated]) AS ID, Format([DateCreated], 'mmm') AS MonthAbbr, Year([DateCreated]) AS Yr, CaseNum FROM tblCase UNION SELECT DISTINCT tblLookupMonth.ID, MonthAbbr, Year([DateCreated]) AS Yr, Null FROM tblLookupMonth, tblCase) AS CaseTrend GROUP BY CaseTrend.ID, CaseTrend.MonthAbbr PIVOT CaseTrend.Yr "
strFiltered = strSQL & strFilter
strFilter = "WHERE ((CaseTrend.ID) Between [Forms]![frmViewStats]![txtBeginDateFilterCase] And [Forms]![frmViewStats]![txtEndDateFilterCase]) "
CurrentDb.OpenRecordset strFiltered
With Me![chtCaseTrend]
.RowSource = strFiltered
.RowSourceType = "Table/Query"
.Enabled = True
.Requery
End With
End If
End Sub
Thanks in advance fellas!