Duane,
Thank you for your input. I thought it was a pretty
simple and straight forward question. This is not going
to be a report based on a specific quarter or date, yet
all four quarters and their totals; therefore the date
range in the query is >#1/1/03#, so that I can include all
projects within that date range. Then, on the report, I
create 10 controls which extract the information I need
for each quarter on the same report. I have also created
a parameter {Enter Channel ID], since the report will be
based per REP or Distributor. I did not create a report
for each rep or distributor because these reports are
changed frequently which would mean changing 30 reports
every time management wants a new field or a format
change. This way I only change 1 report and enter the ID
number of the REP or Distributor I want. Here are the
parameter for each of the controls on the report:
Count of Number of Projects for Each Period (first 5
controls):
1st Quarter =Count(IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0))
2nd Quarter =Count(IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0))
3rd Quarter =Count(IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0))
4th Quarter =Count(IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0))
Year to Date =Count(IIf([ProjectDate] Between
#1/1/03# And #12/31/03#,0))
Total $$ Amount Quoted Projects for Each Period (first 5
controls):
1st Quarter sum based on (IIf([ProjectDate] Between
#1/1/03# And #3/31/03#,0)) - haven't figured out formula
yet.
2nd Quarter sum based on (IIf([ProjectDate] Between
#4/1/03# And #6/30/03#,0)) - haven't figured out formula
yet.
3rd Quarter sum based on (IIf([ProjectDate] Between
#7/1/03# And #9/30/03#,0)) - haven't figured out formula
yet.
4th Quarter sum based on (IIf([ProjectDate] Between
#10/1/03# And #12/31/03#,0)) - haven't figured out formula
yet.
Year to Date
Here is a copy in SQL of the query:
SELECT Projects.QID, Projects.CHID, Channel.Channel,
Projects.ProjectDate, Projects.DeleteDuplicate,
Projects.Revised, Projects.RSMID
FROM Channel INNER JOIN Projects ON Channel.CHID =
Projects.CHID
WHERE (((Projects.CHID)=[Enter Channel ID Number]) AND
((Projects.ProjectDate)>#1/1/2003#) AND
((Projects.DeleteDuplicate)=False) AND ((Projects.Revised)
=False))
WITH OWNERACCESS OPTION;
I appreciate any help you can offer.
Thank you again and have a great day!
Dee
-----Original Message-----
Why would you want to hard-code values into a report? If the report is
limited to the described date range then all you need for a count is:
=Count(*)
How can we suggest an expression to calculate the order total if we don't
know any fields from either the main report or subreport other than
ProjectDate?
--
Duane Hookom
MS Access MVP
Dee said:
Create Control on Report that Sum Order Total Based on
Date. I have created a control in the report footer that
needs to sum the Order Total based on a date range. I
have created this formula that counts the number of orders
based on a date range (and it works): =Count(IIf
([ProjectDate] Between #1/1/2003# And #3/31/2003#,0)), but
I can't find a formula to sum the order total based on
those same dates.
Can Anyone Help??? Please!!
Thank you,
Dee
.
