Sum(iif(...)) Help

  • Thread starter Thread starter Steve D
  • Start date Start date
S

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))
 
Steve D said:
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))
Click to expand...


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.
 
They are fields in the table. t_Year is text and Sales is numeric
--
Thank You,
Steve


Dirk Goldgar said:
Steve D said:
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))
Click to expand...


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)
Click to expand...
 
They are fields in the table. t_Year is text and Sales is numeric
Click to expand...

In that case you need the syntactically required quotemarks:

=Sum(IIf([t_Year]="2007",[Sales],0))
 
I did try to use quotes but I still get #ERROR. ANy other thoughts?
--
Thank You,
Steve


John W. Vinson said:
They are fields in the table. t_Year is text and Sales is numeric
Click to expand...

In that case you need the syntactically required quotemarks:

=Sum(IIf([t_Year]="2007",[Sales],0))
Click to expand...
 
Steve D said:
I didn't name it, it is still called Text48
Click to expand...


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.
 
Linq Adams via AccessMonster.com said:
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.
Click to expand...


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