Sum Where Field?

[anoob]

Hello, this is a quick one:

I have a field that shows the total amount due for all my clients, this is based off a query and set up as follows:

=Sum([JobAndInvoiceData]![AmountDue])

So right now it says essentially - Sum the value of all money due

I would like it to only add the fields where another field named "Status" contains the value "Invoiced". So something like this:

=Sum([JobAndInvoiceData]![AmountDue] WHERE [Status].Invoiced)

I am not sure how to properly type this though - or if "where" is even usable.

Any ideas?
Many thanks.

[RuralGuy]

Add another field to your query that only has a non zero value when [Status] = "invoiced". Then Sum this field instead. As you suspected, there is *no* WHERE condition in the Sum() function as there is with the Domain functions.

[pbaldy]

I like Allan's solution. It can be done with a textbox:

=Sum(IIf(Status = "Invoiced", AmountDue, 0))

[anoob]

Both of these are excellent solutions - thank you very much for your help!

[anoob]

Oh actually here is another quick question: Let's say I have a table that stores transactions from 5 years ago to present, is there a way to take this statement

=Sum(IIf(Status = "Invoiced", AmountDue, 0))

And add something that will make it only apply to transactions in 2010? I believe there are several date-type functions - but I guess I'm not sure if I can wrap THIS code in some sort of Get Year function... any ideas?

Thanks again!

[pbaldy]

=Sum(IIf(Status = "Invoiced" AND Year(TransactionDate) = 2010, AmountDue, 0))

[anoob]

I shall try this now... pbaldy I cannot thank you enough if this works!