I believe your "argument" error was caused by
missing ")" before ",0)"
so you would also lose ending ")"
i.e., there's no such thing as
SUM(somefield, 0)
only
SUM(somefield)
or
SUM(some expression)
But...the SUM needs to be "outside" the IIF...
the IIF part needs to be an expression within the SUM( )
each field in a totals query needs to be either:
(in design grid -- the "Totals" row)
-- group by
-- expression
-- an aggregate function
try this (all one line w/o word wrapping):
Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE >=
[Seminar Tracking Quarters].[Q1 Start Date]
AND
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE <=
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))
or
Q1 NAFYC: SUM(
IIF(
PRODUCTION_SUMMARY_CONCATENATED.SOURCE_DATE
BETWEEN
[Seminar Tracking Quarters].[Q1 Start Date]
AND
[Seminar Tracking Quarters].[Seminar Start Date] ,
PRODUCTION_SUMMARY_CONCATENATED.MO_NAFYC_CR_AMT ,0))
Erika M. said:
Here is what I have now:
Q1 NAFYC:IIf( [PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] >= [Seminar
Tracking Quarters]![Q1 Start Date] And
[PRODUCTION_SUMMARY_CONCATENATED]![SOURCE_DATE] <= [Seminar Tracking
Quarters]![Seminar Start Date] ,Sum(
[PRODUCTION_SUMMARY_CONCATENATED]![MO_NAFYC_CR_AMT] ,0))
But now I am getting an error message stating that I have the wrong
number
of arguments. Help!!!
Tom Lake said:
I am trying to write an expression in a date field in a query that
says:
IIf([Date] isBetween [Q1 Start Date] And [Seminar Start Date],
Sum([Production Amount],0))
I know I have it written wrong here - but I don't know how to do an
IIf....Between....And....
Please help!! I have been working on this query in hopes to build a
report
off of it for weeks now - and I have hit a wall. I appreciate any
assistance.
Thanks. You all are great!
I'd do this:
IIf([Date] >= [Q1 Start Date] And [Date] <= [Seminar Start Date],
Sum([Production Amount], 0))
Tom Lake