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

BigToe · #1 (**permalink**) 01-23-12, 19:46

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!

Sinndho · #2 (**permalink**) 01-24-12, 04:29

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:

Quote:

([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.

BigToe · #3 (**permalink**) 01-30-12, 13:37

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!

Sinndho · #4 (**permalink**) 01-30-12, 15:12

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

BigToe · #5 (**permalink**) 01-30-12, 16:16

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.

BigToe · #6 (**permalink**) 01-31-12, 12:58

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!

Sinndho · #7 (**permalink**) 01-31-12, 13:28

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

BigToe · #8 (**permalink**) 02-03-12, 19:45

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?

Sinndho · #9 (**permalink**) 02-04-12, 01:51

Quote:

Originally Posted by BigToe

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech