Sum(iif(...)) Help

Good Afternoon,
I am looking to do a sum if in a form footer but I keep getting the
result #ERROR. Here is the formula I have, it matched a number of responses
on this board but does not seem to work. Any help would be appreciated.

=Sum(IIf([t_Year]=2007,[Sales],0))
--
Thank You,
Steve

"Steve D" wrote in message
...
Good Afternoon,
I am looking to do a sum if in a form footer but I keep getting the
result #ERROR. Here is the formula I have, it matched a number of
responses
on this board but does not seem to work. Any help would be appreciated.

=Sum(IIf([t_Year]=2007,[Sales],0))


Are "t_Year" and "Sales" then names of fields in the form's recordsource?
They can't just be names of controls on the form.

Are t_Year and Sales numeric fields, not text?

Make sure the name of the text box with this controlsource expression isn't
the name of any field in the form's recordsource.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

They are fields in the table. t_Year is text and Sales is numeric
--
Thank You,
Steve


"Dirk Goldgar" wrote:

"Steve D" wrote in message
...
Good Afternoon,
I am looking to do a sum if in a form footer but I keep getting the
result #ERROR. Here is the formula I have, it matched a number of
responses
on this board but does not seem to work. Any help would be appreciated.

=Sum(IIf([t_Year]=2007,[Sales],0))


Are "t_Year" and "Sales" then names of fields in the form's recordsource?
They can't just be names of controls on the form.

Are t_Year and Sales numeric fields, not text?

Make sure the name of the text box with this controlsource expression isn't
the name of any field in the form's recordsource.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On Tue, 27 Oct 2009 13:02:01 -0700, Steve D
wrote:

They are fields in the table. t_Year is text and Sales is numeric

In that case you need the syntactically required quotemarks:

=Sum(IIf([t_Year]="2007",[Sales],0))
--

John W. Vinson [MVP]

I did try to use quotes but I still get #ERROR. ANy other thoughts?
--
Thank You,
Steve


"John W. Vinson" wrote:

On Tue, 27 Oct 2009 13:02:01 -0700, Steve D
wrote:

They are fields in the table. t_Year is text and Sales is numeric

In that case you need the syntactically required quotemarks:

=Sum(IIf([t_Year]="2007",[Sales],0))
--

John W. Vinson [MVP]
.

"Steve D" wrote in message
...
I did try to use quotes but I still get #ERROR. ANy other thoughts?


What is the name of the text box containing the expression?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

I didn't name it, it is still called Text48
--
Thank You,
Steve


"Dirk Goldgar" wrote:

"Steve D" wrote in message
...
I did try to use quotes but I still get #ERROR. ANy other thoughts?


What is the name of the text box containing the expression?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Steve D" wrote in message
...
I didn't name it, it is still called Text48


Please check your references to see if any of them is broken. To do that,
in the VB editor environment click Tools - References..., and look for any
referenced marked as "MISSING".

If that's not it, I'm perplexed. If you'd like to send me a cut-down copy
of your database, containing only the elements necessary to demonstrate the
problem, compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it to
the address derived by removing NO SPAM and ".invalid" from the reply
address of this message. If that address isn't visible to you, you can get
my address from my web site, which is listed in my sig. Do *not* post my
real address in the newsgroup -- I don't want to be buried in spam and
viruses.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Is the field [Sales] a field where data is entered, or is it a calculated
field? If I remember correctly, you cannot run aggregate functions like SUM
against a field that is itself a calculated field, you have to run it against
the expression used to derive the field.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

"Linq Adams via AccessMonster.com" u28780@uwe wrote in message
news:9e559050b5e01@uwe...
Is the field [Sales] a field where data is entered, or is it a calculated
field? If I remember correctly, you cannot run aggregate functions like
SUM
against a field that is itself a calculated field, you have to run it
against
the expression used to derive the field.


You can't Sum a calculated *control*, but you can certainly Sum a calcuated
*field* in the form's recordsource.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)