Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    33

    Unanswered: need report to return data from selected year and quarter plus 8 Q back!

    I hope someone can help with this problem. I have two columns to identify the year and quarter for the data that I'm collecting but my challenge is to find a way to have the user enter any year and quarter and have the query/report include 8 quarters back from the year/quarter selected. For example, if I select 2011 and Q1 I'll need data from:
    2011 Q1
    2010 Q4
    2010 Q3
    2010 Q2
    2010 Q1
    2009 Q4
    2009 Q3
    2009 Q2

    issues:
    I know that having a column just for the year does not represent a date so I can't use date criteria to filter (or can I?)

    If I use criteria to show quarter 1 then it will also show quarter one for 2009 (in this example) which is undesirous.

    Any thoughts would be greatly appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This function will return the 'WHERE' clause of a query corresponding to the Year and Quarter values passed as parameters.
    Code:
    Function BuildQueryFilter(ByVal SelectedYear As Long, ByVal Quarter As String) As String
    
        Dim i As Long
        Dim cnt As Long
        
        Do
            If Len(BuildQueryFilter) > 0 Then BuildQueryFilter = BuildQueryFilter & " OR "
            BuildQueryFilter = BuildQueryFilter & "([Year] = " & SelectedYear & " AND [Quarter] = '" & Quarter & "')"
            i = Right(Quarter, 1)
            Quarter = Choose(i, "Q4", "Q1", "Q2", "Q3")
            If i = 1 Then SelectedYear = SelectedYear - 1
            cnt = cnt + 1
            If cnt = 8 Then Exit Do
        Loop
        
    End Function
    Example:
    Code:
    Debug.Print BuildQueryFilter( 2011, "Q1")
    Returns:
    ([Year] = 2011 AND [Quarter] = 'Q1') OR ([Year] = 2010 AND [Quarter] = 'Q4') OR ([Year] = 2010 AND [Quarter] = 'Q3') OR ([Year] = 2010 AND [Quarter] = 'Q2') OR ([Year] = 2010 AND [Quarter] = 'Q1') OR ([Year] = 2009 AND [Quarter] = 'Q4') OR ([Year] = 2009 AND [Quarter] = 'Q3') OR ([Year] = 2009 AND [Quarter] = 'Q2')
    Notes:
    1. If the names of the columns in the table are not 'Year' and 'Quarter', adapt the code accordingly.
    2. The criteria should be optimized to use the quarter only when the year is not full.
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    33
    Hi again,
    thank you so much for this help! I've been trying for a few days now to having a user define the values for year and quarter with a form, turn them into variables and have your code use those variables which then get used as criteria in a query. I have been using this link as reference as I'm using Access 2007:
    Using a VBA Variable to Filter a Query in Access 2007

    but the process eludes me. I have the following code to open the query via a button on the form:
    Code:
    Private Sub Command66_Click()
    Dim varRptYear As Long
    Dim varRptQ As Long
    
    Me.RptYear = varRptYear
    Me.RptQ = varRptQ
    
    YearPub Me.varRptYear
    Qpub Me.varRptQ
    
    DoCmd.OpenQuery "IndicatorRptQ"
    End Sub
    Am I on the right track?
    thanks again!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How is your "Command66_Click" procedure related to the code I supplied and what are "YearPub" and "Qpub"?

    Also, this is useless:
    Code:
    Dim varRptYear As Long
    Dim varRptQ As Long
    Me.RptYear = varRptYear
    Me.RptQ = varRptQ
    You don't supply any value for "varRptYear" and "varRptQ" (which are strangely prefixed with "var" while they are declared as Long), so it's equivalent to:
    Code:
    Me.RptYear = 0
    Me.RptQ = 0
    Have a nice day!

  5. #5
    Join Date
    Apr 2010
    Posts
    33
    Perhaps I should not have given an example of my attempt to impliment your code because I don't really have much skill in this sort of thing.

    the Command66_click proceedure is meant to execute a query that takes the user's input from the text fields of a form (for year and quarter) and uses that input as the year and quarter variables needed to use your code for the WHERE criteria in the query.

  6. #6
    Join Date
    Apr 2010
    Posts
    33
    I have created a form with two fields, txtYear and txtQ, and a button with the hopes that I can have the user enter the year and quarter and click the button to set values for the variables SelectedYear and Quarter in the function above. I also would like the results of the function to be inserted as criteria in a query. What would the code look like to accomplish these tasks? Any help would be appreciated!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the two Text Boxes (not Fields) are named "txtYear" and "txtQ" and if the command button is named "cmdGetFilter", then the code for calling the function "BuildQueryFilter" is as follows:
    Code:
    Private Sub cmdGetFilter_Click()
    
        Dim strFilter As String
        
        strFilter = BuildQueryFilter(Me.txtYear.value, Me.txtQ.value)
        
    End Sub
    Once you have the criteria (in "strFilter") you can use "strFilter" as a "WhereCondition" parameter to open another Form:
    Code:
        DoCmd.OpenForm "MyForm", , , strFilter
    Or a Report:
    Code:
    DoCmd.OpenReport "MyReport", , , strFilter
    You can also use it to create a new Query or modify an existing one:
    Code:
        Dim strSQL as String
    
        strSQL = "SELECT * FROM MyTable WHERE " & strFilter
        CreateQuery "MyQuery", strSQL
    With a procedure "CreateQuery" such as:
    Code:
    Sub CreateQuery(ByVal QueryName As String, ByVal SQL As String)
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If qdf.Name = QueryName Then Exit For
        Next qdf
        If Not qdf Is Nothing Then
            qdf.SQL = SQL
        Else
            Set qdf = dbs.CreateQueryDef(QueryName, SQL)
        End If
        dbs.QueryDefs.Refresh
        Set qdf = Nothing
        Set dbs = Nothing
            
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Apr 2010
    Posts
    33

    wow!

    wow, it's working! I can't believe it. Some things I was not aware of, that the Wherecondition parameter can be connected to a report or form but not a query - which seems kind of odd to me. ex:
    Code:
    DoCmd.OpenQuery "MyQuery", , , strFilter
    I can see the advantage where I can use that Wherecondition parameter for other reports needing the same criteria, but one snag I came across is the option to put a graph in the report which uses data pulled from the query, that does not have the Wherecondition. You mention you can create a new or modify an existing query by adding the BuildQueryFilter function but your example seems to be for creating a new query (which works great BTW) but I'm a little stuck as to how to modify an existing query - assuming that would solve the chart issue I have. Perhaps there is another workaround?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by BigToe View Post
    You mention you can create a new or modify an existing query by adding the BuildQueryFilter function but your example seems to be for creating a new query (which works great BTW) but I'm a little stuck as to how to modify an existing query - assuming that would solve the chart issue I have. Perhaps there is another workaround?
    The function CreateQuery I provided can do both:
    a) If a query named by the parameter QueryName exists, it's SQL statement is replaced by what is passed in the SQL parameter.
    b) If such a query does not exist, it is created.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •