Ok...it sounds like you have a table with a date field. And what you want is
a report with 4 sections, corresponding to the 4 quarters of a year. And
then the ability to total or compare the numbers from each quarter on a
report footer. Forget what I was saying in the earlier posts...I didn;t get
what you were looking for. There might be a better way to do it, but this
will work.
What you need to do is manually (via code) build a query that will retrieve
the data you need. One of the fields will be a calculated field that figured
out what quarter a give record is in. Then, in the OnLoad event of the
report, you modify the query to use the desired dates, and apply the query to
the report.
Step 1 is to just get the query working (forget about changing the dates for
a moment):
Assumes you have a table called Invoices, with 3 fields: InvNum, InvAmount,
and InvDate. The following query retuirns those 3 fields. It also uses 4
embedded IIF statements to create a fourth field called exprQuarter. This
calculated field will contain a 0 thru 4 depending on which quarter the
InvDate falls into. If the dates fall between 1/1/2009 and 3/31/2009,
exprQuarter will be 1, if the dates fall between 4/1/2009 and 6/30/2009
exprQuarter will be 2, etc. If the dates fall outside all of the ranges,
then exprQuarter will be 0.
Select InvNum, InvAmount, InvDate, exprQuarter: IIf([InvDate] Between
#1/1/2009# And #3/31/2009#,1,IIf([InvDate] Between #4/1/2009# And
#6/30/2009#,2,IIf([InvDate] Between #7/1/2009# And
#9/30/2009#,3,IIf([InvDate] Between #10/1/2009# And #12/31/2009#,4,0))))
From Invoices WHERE InvDate between #1/1/2009# and #12/31/2009#
To start with, don't worry about applying the dates from your text boxes.
Just get the query working as a query with hard coded dates.
Step 2 is to create the report, based off of the query. Once it is
returning data, add grouping based on the exprQuarter field. That will allow
you to sum, average, etc. in the group footer as well as do whatever
calculations you need in the report footer.
Step 3 is to create an OnLoad event for the report. For the example below,
I assume the report is named "MyReportName", and the form with the text boxes
is called "MyFormName".
Copy the SQL from your query object and paste it as a string.
Sub MyReportName_OnLoad
' Declare 8 string variables to hold the dates you want to use
DIM D1, D2, D3, D4, D5, D6, D7, D8 As String
' and another string variable to hold our SQL statement
DIM SQL as String
' If the form with the text boxes isn;t loaded, trying to reference them
will generate an error
If IsLoaded("MyFormName") Then
' Here, you can either go back to the form with your text boxes like this...
D1 = Forms.MyFormName.TextBox1
D2 = Forms.MyFormName.TextBox2
... ' Add in the lines for 3-7
D8 = Forms.MyFormName.TextBox8
' Another option would be to build an 8 element array in the calling form,
and pass that
' as a variant to the report using the OpenArgs parameter.
' Notice that I've replaced the dates in the query with %1, %2, %3, etc
SQL = "Select InvNum, InvAmount, InvDate, exprQuarter: & _
"IIf([InvDate] Between #%1# And #%2#,1," & _
"IIf([InvDate] Between #%3# And #%4#,2," & _
"IIf([InvDate] Between #%5# And #%6#,3," & _
"IIf([InvDate] Between #%7# And #%8#,4,0)))) " & _
"From Invoices WHERE InvDate between #%1# and #%8#"
' Here, we replace %1 with the first date
SQL = Replace(SQL, "%1", D1)
SQL = Replace(SQL, "%2", D2)
... ' Add in the lines for 3-7
SQL = Replace(SQL, "%8", D8)
' Also note that the Where clause of the query uses %1 and %8, so both
instances of %1 will get replaced
' Ditto with %8
' Then, you just use the apply the SQL variable to the reports
RecordSource property
Me.RecordSource = SQL
End If
End Sub
One final note: If you need to make changes to the query, you have to
change both the query itself, and the text version in the OnOpen sub. The
query tells the report what fields are available in design mode. If you were
to just add a new field to the query text in the sub, then that field will
never show up in the field list to put on the report. On the other hand, if
you only added the new field in the query designer, it would let you put it
on the report, but it will generate an error. When the report is run and the
query is replaced in the OnOpen event, the control will point to a field that
doesn't exist.
AJ said:
[QuarterStart] is the name of the text box on the form (Form1). I should try
to be a little more clear.
I have a form with 8 text boxes. the user enters in the Quarter they want
the results to start [QuarterStart], and the Year [Year1]. Then the form
automatically fills in the previous 3 quarters ([Q2],[Q3],[Q4]) and
corresponding year([Year2],[Year3],[Year4]).
I have a query (Query1) that calculates a result from some other queries,
but it needs input from the user (user enters the desired Quarter and Year).
So, what I want to do is to run "Query1" 4 times, to find results from 4
different quarters, and have all 4 results show up on one report so that I
can calculate an average of those results.
Hopefully that makes more sense.
Ray said:
Hi,
I'm not clear on what you are trying to do. What is the date field in the
database you are filtering on? Is [QuarterStart] the name of the database
field or the name of the text box?
Imagine I have a database of people with Birthdays that has 2 fields, [Name]
and [Bday]. I also have a form with 4 text boxes named Text1, Text2, Text3,
and Text4. I also have a report named "rptBDay" that shows the names and
birthdays. Now look at the following code:
Private Sub Command0_Click()
Dim FilterStr As String
Dim D1 As String
Dim D2 As String
Dim D3 As String
Dim D4 As String
D1 = Forms.Form1.Text1
D2 = Forms.Form1.Text2
D3 = Forms.Form1.Text3
D4 = Forms.Form1.Text4
FilterStr = "[Bday] = '" & D1 & "' OR [Bday] = '" & D2 & "' OR [Bday] =
'" & D3 & "' OR [Bday] = '" & D4 & "'"
' This just shows the filterstring for troubleshooting
MsgBox FilterStr
DoCmd.OpenReport "rptBDay", , , FilterStr
End Sub
The above code prints a single report showing anyone whose birthday falls on
any of the 4 dates I typed in the boxes.
For example, if I type in 1/1/2009, 1/2/2009, 1/3/2009, and 1/4/2009, the
report will show me people whose birthday is any of the first 4 days of 2009.
Ray
:
Thanks Ray! I've tried to build what you've posted, but I think I am still a
little confused.
So this is what I have, and it's giving me a "compile error" highlighting
the DoReport........somehow I don't think I've got it right.......also I
don't understand what the "date + 2" etc is for.......so I left that part out
in mine......
Private Sub OK_Click()
DoCmd.DoReport
Dim FilterStr As String
Dim D1 As Date
Dim D2 As Date
Dim D3 As Date
Dim D4 As Date
D1 = [Forms]![Form1]![QuarterStart]
D2 = [Forms]![Form1]![Quarter2]
D3 = [Forms]![Form1]![Quarter3]
D4 = [Forms]![Form1]![QuarterEnd]
FilterStr = "[QuarterStart] = '" & D1 & "' OR [Quarter2] = '" & D2 & "'
OR "
[Quarter3] = '" & D3 & "' OR [QuarterEnd]= '" & D4 & "'"
DoCmd.OpenReport "Report1", , , FilterStr
End Sub
:
Use the filter parameter to the DoCmd.OpenReport command. Something like
below. Replace the variable assignments (D1 = Date) with wherever you are
getting the dates. Or you could modify the sub to take the dates as
parameters.
Private Sub DoReport
Dim FilterStr As String
Dim D1 As Date
Dim D2 As Date
Dim D3 As Date
Dim D4 As Date
D1 = Date
D2 = Date + 2
D3 = Date + 4
D4 = Date + 6
FilterStr = "DateField = '" & D1 & "' OR DateField = '" & D2 & "' OR
DateField = '" & D3 & "' OR DateField = '" & D4 & "'"
DoCmd.OpenReport "ReportName", , , FilterStr
End Sub
:
I am lost in Visual Basic.......
I want to run the same query 4 times using 4 unique dates, but have the
results show up on a single report. Is there some VB code that I can use to
run the same query mulitple times using dates that are inputed? I have a
query built to select the results from a single date, and a form that you can
input 4 dates, and a command button on the form runs the query. But I cannot
figure out how to get the command button to run the query 4 times with the 4
different dates.
Any help greatly appreciated!!
