IIF sum question

  • Thread starter Thread starter margaret
  • Start date Start date
M

margaret

I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's also
adding the amount for other years.

Help. thanks
 
margaret said:
I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's also
adding the amount for other years.
Click to expand...


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
 
So I changed by control source:

=IIf([CTRLYEAR]=[First Year?],Sum([amount]))

What I'm attempting to do (and you might know an easier way) is show a
report with a two year comparison. So one column would show [first year?]
and the next column would show [second year?].

However, it's still adding both 2008 and 2009 figures. Sometimes it will
put the total in the first control source ([firstamount]) and sometimes in
the second control source ([secondamount])

I don't want to put the actual year in, because next year the first year
might be 2010 and then you can choose the second year as either 2008 or 2009.

hoping this makes sense,
margaret

Marshall Barton said:
margaret said:
I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's also
adding the amount for other years.
Click to expand...


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
Click to expand...
 
You mixed up the Sum and IIf. Check my suggested expression
more carefully.
--
Marsh
MVP [MS Access]

So I changed by control source:

=IIf([CTRLYEAR]=[First Year?],Sum([amount]))

What I'm attempting to do (and you might know an easier way) is show a
report with a two year comparison. So one column would show [first year?]
and the next column would show [second year?].

However, it's still adding both 2008 and 2009 figures. Sometimes it will
put the total in the first control source ([firstamount]) and sometimes in
the second control source ([secondamount])

I don't want to put the actual year in, because next year the first year
might be 2010 and then you can choose the second year as either 2008 or 2009.

hoping this makes sense,
margaret

Marshall Barton said:
margaret said:
I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's also
adding the amount for other years.
Click to expand...


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
Click to expand...
Click to expand...
 
Marsh's advice will reference the current records in your report. This is
generally what you want to happen. For instance, if a report is open with a
filter or where condition like:
[Region]= "West"
You would almost always want the sum of amount for 2009 to be only the West
Region (those in the report's output).

If you use $teve'$ solution, you will always display the total for all
records where the CTRLYEAR = 2009. This is generally not what you want.
--
Duane Hookom
Microsoft Access MVP


Marshall Barton said:
You mixed up the Sum and IIf. Check my suggested expression
more carefully.
--
Marsh
MVP [MS Access]

So I changed by control source:

=IIf([CTRLYEAR]=[First Year?],Sum([amount]))

What I'm attempting to do (and you might know an easier way) is show a
report with a two year comparison. So one column would show [first year?]
and the next column would show [second year?].

However, it's still adding both 2008 and 2009 figures. Sometimes it will
put the total in the first control source ([firstamount]) and sometimes in
the second control source ([secondamount])

I don't want to put the actual year in, because next year the first year
might be 2010 and then you can choose the second year as either 2008 or 2009.

hoping this makes sense,
margaret

Marshall Barton said:
margaret wrote:

I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's also
adding the amount for other years.


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
Click to expand...
Click to expand...
Click to expand...
 
$teve,
Can't you read? I didn't see "for all records" in the original or any other
post from margaret. Did I miss something? I'm using the web interface to the
news group.

I also didn't state your solution was wrong. I just suggested that generally
a total in a report should reflect the sum of values in the report. I would
bet this is true in a majority of the reports I have ever created or have
read about from other developers. The use of Marsh's solution is much more
efficient since it doesn't need to create another query of a recordset like
the DSum().

If you are going to criticize someone, you should at least get your facts
straight and possibly even learn something before you put your foot in your
mouth.

--
Duane Hookom
Microsoft Access MVP


Steve said:
Can't you read???

The OP said, " I attempting to add only the tblSales.amount for 2009".

She wants to display the total for all records where the CTRLYEAR = 2009.

Steve



Duane Hookom said:
Marsh's advice will reference the current records in your report. This is
generally what you want to happen. For instance, if a report is open with
a
filter or where condition like:
[Region]= "West"
You would almost always want the sum of amount for 2009 to be only the
West
Region (those in the report's output).

If you use $teve'$ solution, you will always display the total for all
records where the CTRLYEAR = 2009. This is generally not what you want.
--
Duane Hookom
Microsoft Access MVP


Marshall Barton said:
You mixed up the Sum and IIf. Check my suggested expression
more carefully.
--
Marsh
MVP [MS Access]


margaret wrote:
So I changed by control source:

=IIf([CTRLYEAR]=[First Year?],Sum([amount]))

What I'm attempting to do (and you might know an easier way) is show a
report with a two year comparison. So one column would show [first
year?]
and the next column would show [second year?].

However, it's still adding both 2008 and 2009 figures. Sometimes it
will
put the total in the first control source ([firstamount]) and sometimes
in
the second control source ([secondamount])

I don't want to put the actual year in, because next year the first year
might be 2010 and then you can choose the second year as either 2008 or
2009.

hoping this makes sense,
margaret

:

margaret wrote:

I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However, it's
also
adding the amount for other years.


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
Click to expand...
Click to expand...
Click to expand...
 
$teve,
If you reply, please answer each of these questions:

Are you suggesting that every OP understands reports, record sources,
tables, aggregates, etc?

Do you feel that your solution would be more efficient than Marsh's?

Do you think there is a chance the OP wanted a sum of the amounts for the
records in the report where the CTRLYEAR = 2009?

Do you think that I stated your answer was wrong?

Do you think that we should provide greater assistance than simply
providing an answer that might work in some situations but might be
inaccurate in other situations?

--
Duane Hookom
Microsoft Access MVP


Steve said:
"tblSales.amount for 2009" implicitly says all records for 2009. It doesn't
take a rocket scientist to understand that!!

Steve


Duane Hookom said:
$teve,
Can't you read? I didn't see "for all records" in the original or any
other
post from margaret. Did I miss something? I'm using the web interface to
the
news group.

I also didn't state your solution was wrong. I just suggested that
generally
a total in a report should reflect the sum of values in the report. I
would
bet this is true in a majority of the reports I have ever created or have
read about from other developers. The use of Marsh's solution is much more
efficient since it doesn't need to create another query of a recordset
like
the DSum().

If you are going to criticize someone, you should at least get your facts
straight and possibly even learn something before you put your foot in
your
mouth.

--
Duane Hookom
Microsoft Access MVP


Steve said:
Can't you read???

The OP said, " I attempting to add only the tblSales.amount for 2009".

She wants to display the total for all records where the CTRLYEAR = 2009.

Steve



Marsh's advice will reference the current records in your report. This
is
generally what you want to happen. For instance, if a report is open
with
a
filter or where condition like:
[Region]= "West"
You would almost always want the sum of amount for 2009 to be only the
West
Region (those in the report's output).

If you use $teve'$ solution, you will always display the total for all
records where the CTRLYEAR = 2009. This is generally not what you want.
--
Duane Hookom
Microsoft Access MVP


:

You mixed up the Sum and IIf. Check my suggested expression
more carefully.
--
Marsh
MVP [MS Access]


margaret wrote:
So I changed by control source:

=IIf([CTRLYEAR]=[First Year?],Sum([amount]))

What I'm attempting to do (and you might know an easier way) is show
a
report with a two year comparison. So one column would show [first
year?]
and the next column would show [second year?].

However, it's still adding both 2008 and 2009 figures. Sometimes it
will
put the total in the first control source ([firstamount]) and
sometimes
in
the second control source ([secondamount])

I don't want to put the actual year in, because next year the first
year
might be 2010 and then you can choose the second year as either 2008
or
2009.

hoping this makes sense,
margaret

:

margaret wrote:

I have a control source and I put this in:

=IIf([CTRLYEAR]="2009",Sum([tblSales.amount]))

I attempting to add only the tblSales.amount for 2009. However,
it's
also
adding the amount for other years.


If CtrlYear is a number type field, the 2009 should not have
quotes around it.

=Sum(IIf(CTRLYEAR=2009,amount,0))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top