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:
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.
This function will return the 'WHERE' clause of a query corresponding to the Year and Quarter values passed as parameters.
Function BuildQueryFilter(ByVal SelectedYear As Long, ByVal Quarter As String) As String
Dim i As Long
Dim cnt As Long
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
Debug.Print BuildQueryFilter( 2011, "Q1")
([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')
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.
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:
Private Sub Command66_Click()
Dim varRptYear As Long
Dim varRptQ As Long
Me.RptYear = varRptYear
Me.RptQ = varRptQ
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.
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!
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:
Private Sub cmdGetFilter_Click()
Dim strFilter As String
strFilter = BuildQueryFilter(Me.txtYear.value, Me.txtQ.value)
Once you have the criteria (in "strFilter") you can use "strFilter" as a "WhereCondition" parameter to open another Form:
DoCmd.OpenForm "MyForm", , , strFilter
Or a Report:
DoCmd.OpenReport "MyReport", , , strFilter
You can also use it to create a new Query or modify an existing one:
Dim strSQL as String
strSQL = "SELECT * FROM MyTable WHERE " & strFilter
CreateQuery "MyQuery", strSQL
With a procedure "CreateQuery" such as:
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
If Not qdf Is Nothing Then
qdf.SQL = SQL
Set qdf = dbs.CreateQueryDef(QueryName, SQL)
Set qdf = Nothing
Set dbs = Nothing
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:
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?
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.