Sum(iif(...)) Help

[Steve D]

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))

 

[Dirk Goldgar]

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.

 

[Steve D]

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

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)

 

[John W. Vinson]

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))

 

[Steve D]

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

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))

 

[Dirk Goldgar]

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?

 

[Steve D]

I didn't name it, it is still called Text48

 

[Dirk Goldgar]

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]

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.