Results 1 to 11 of 11
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Filter Form Chart by Date

    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.



    Click image for larger version. 

Name:	123.PNG 
Views:	16 
Size:	12.1 KB 
ID:	32105

    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!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do the concatenation after setting the variable.

    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 "
    strFilter = "WHERE ((CaseTrend.ID) Between [Forms]![frmViewStats]![txtBeginDateFilterCase] And [Forms]![frmViewStats]![txtEndDateFilterCase]) "
    strFiltered = strSQL & strFiltered
    CurrentDb.OpenRecordset strFiltered


    Also, probably need to use PARAMETERS in the CROSSTAB. Review: http://allenbrowne.com/ser-67.html#Param
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I do not believe it will allow more than one parameter in the crosstab, tried that before.

    I get this message when opening form.

    Click image for larger version. 

Name:	12.PNG 
Views:	16 
Size:	16.1 KB 
ID:	32107

    I get this message when I stop/bypass the error code to proceed to the form.
    Click image for larger version. 

Name:	1234.PNG 
Views:	16 
Size:	28.6 KB 
ID:	32108

    I get this message when I have a date range in the boxes and press the filter button.
    Click image for larger version. 

Name:	12345.PNG 
Views:	16 
Size:	13.4 KB 
ID:	32109

    Seems like a possible error with the concatenation when it comes after the WHERE but does not do that in the original post order.

    Perhaps if I do a filter by the date ranges then apply the strSQL query to it? However, they may not show all the months of the year as desired.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have managed to build all my graphs without the use of CROSSTAB query. They are all XYScatter type.

    Try concatenating the control references and using date/time delimiters.

    strFilter = "WHERE ((CaseTrend.ID) Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase]) & "#"

    Did you add PARAMETERS to the statement?

    I can better analyze graph issues working with data. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The aforementioned post was just making modifications recommended.

    After trying the original way with recommendation added I decided to add the ID for Months and Yr for Year since both would require evaluating.

    strFilter = "WHERE ((CaseTrend.ID, CaseTrend.Yr) Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#) "

    Still no dice but at least it knows the field from the table DateCreated is the field of interest. The following is the error.
    Click image for larger version. 

Name:	145.PNG 
Views:	16 
Size:	10.3 KB 
ID:	32110

  6. #6
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Original as show in the chart.

    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;

    New attempt to filter date range. The query design is what placed the WHERE clause after adding a criteria. So for all I know it is jacked as other things I have come to discover.

    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;

  7. #7
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    With this:

    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 WHERE (((CaseTrend.ID) Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#)) GROUP BY CaseTrend.ID, CaseTrend.MonthAbbr PIVOT CaseTrend.Yr "

    CurrentDb.OpenRecordset strSQL

    With Me![chtCaseTrend]
    .RowSource = strSQL

    At least I get this without errors so long as I have a date in both text boxes:

    Click image for larger version. 

Name:	1456.PNG 
Views:	16 
Size:	2.5 KB 
ID:	32111

    After clicking filter button without date in text boxes:

    Click image for larger version. 

Name:	14567.PNG 
Views:	16 
Size:	10.7 KB 
ID:	32112

    Even if only able to filter by year (i.e. only show years of interest) and not specific months would suffice the requirement.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I expect your If conditional is not correct. Try:

    If [Forms]![frmViewStats]![txtBeginDateFilterCase] & "" = "" Or [Forms]![frmViewStats]![txtEndDateFilterCase] & "" = "" Then


    Or provide alternate values like 1/1/1900 and 12/31/2099.


    Yes, CROSSTAB will take multiple parameters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Looks to me like June's latest suggestion should be enough to get this working.

    If not, you might be interested in looking at this free app which is available on my website.
    http://www.mendipdatasystems.co.uk/free-apps/4592091376

    The chart shown below uses a crosstab query as its record source

    Click image for larger version. 

Name:	CrosstabChartExample.PNG 
Views:	16 
Size:	71.2 KB 
ID:	32116

    Code:
    TRANSFORM First(qryExchangeRates.Rate) AS FirstOfRate SELECT qryExchangeRates.Date FROM qryExchangeRates WHERE (((qryExchangeRates.Date) Is Not Null) AND ((qryExchangeRates.Use)=True) AND ((qryExchangeRates.Base)=GetBaseCode())) GROUP BY qryExchangeRates.Date ORDER BY qryExchangeRates.Date, qryExchangeRates.CurrencyCode PIVOT qryExchangeRates.CurrencyCode;
    Here's another example using a crosstab query with a date range:

    Click image for larger version. 

Name:	CrosstablDateChartExample.PNG 
Views:	17 
Size:	44.7 KB 
ID:	32117

    Code:
    Me.GraphMonth.RowSource = "TRANSFORM Sum(qryCountMessageLogMonth.Total) AS SumOfTotal" & _            
                " SELECT qryCountMessageLogMonth.MonthYear" & _
                " FROM qryCountMessageLogMonth" & _
                " WHERE (((qryCountMessageLogMonth.Date) Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & "#" & _
                " AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "#))" & _
                " GROUP BY qryCountMessageLogMonth.MonthNo, qryCountMessageLogMonth.MonthYear" & _
                " ORDER BY qryCountMessageLogMonth.MonthNo" & _
                " PIVOT qryCountMessageLogMonth.MessageType;"
    I also have many other examples of crosstab charts including the use of date ranges if that is helpful to you
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Applied last recommendations and the following error appears. Suggests what I surmised earlier that it may be a concatenation error due to the "PIVOT" part coming before the WHERE Clause.

    Click image for larger version. 

Name:	147.PNG 
Views:	16 
Size:	13.4 KB 
ID:	32118

    I will try reordering some things to see if that fixes the problem.

  11. #11
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Alright folks. So it was a concatenation error when trying to append the WHERE clause following the PIVOT portion of the chart SQL statement. The following is the complete code that is similar to the original post but with the necessary changes (red) and recommendations (blue). BTW I am sure I already tried troubleshooting the concatenation error before but I believe the recommendation (blue) is what helped that issue.

    Private Sub chtCaseTrend_GotFocus()
    Dim strSQL As String
    If [Forms]![frmViewStats]![txtBeginDateFilterCase] & "" = "" Or [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 WHERE ((DateCreated) Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#) 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 "
    CurrentDb.OpenRecordset strSQL
    With Me![chtCaseTrend]
    .RowSource = strSQL
    .RowSourceType = "Table/Query"
    .Enabled = True
    .Requery
    End With
    End If
    End Sub

    This is the final code for the SQL of interest. If anyone sees any potential issue with this then please feel free to state so.

    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
    WHERE (((DateCreated) Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#))
    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;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter A Web Form By Date
    By Jaynen in forum Macros
    Replies: 0
    Last Post: 12-27-2016, 09:45 AM
  2. Limit chart to date range , Chart isnt updating .
    By fluffyvampirekitten in forum Access
    Replies: 5
    Last Post: 06-30-2015, 12:27 AM
  3. Replies: 11
    Last Post: 06-28-2015, 06:42 PM
  4. Chart filter based on header value
    By trivanka in forum Reports
    Replies: 3
    Last Post: 02-14-2013, 05:23 PM
  5. Replies: 2
    Last Post: 04-17-2012, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums