# Thread: Sum IIf (2002 SP-2)

1. ## Sum IIf (2002 SP-2)

I've tried this so many ways now that I can't even recall all the ways I've tried <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

= Sum(IIf( [InvoiceDate] = Between (Date()-15) And (Date()-29), [TotalBilledCalc] ,0))

If not obvious, this is for a txtbx to return total aging for the period. Little help?

2. ## Re: Sum IIf (2002 SP-2)

=Sum(IIf([InvoiceDate] Between Date()-29 And Date()-15,[TotalBilledCalc],0))

3. ## Re: Sum IIf (2002 SP-2)

. . .grrrrrr
You'd think as often as I neglect to include brackets, leaving them out wouldn't be a problem <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Thanks Hans!

4. ## Re: Sum IIf (2002 SP-2)

Hmmm. The same principle doesn't work for "Count"?

=Count(IIf([InvoiceDate] Between Date()-29 And Date()-15,[InvoiceDate],0))

Returns all records (not those between 15 and 29 days)

5. ## Re: Sum IIf (2002 SP-2)

The Count function, as the name indicates, counts the number of records, it doesn't matter whether the value is 0 or otherwise. To create a conditional count, you still use Sum:

=Sum(IIf([InvoiceDate] Between Date()-29 And Date()-15,1,0))

If the invoicedate is in the specified period, it counts as 1, otherwise as 0. The Sum function counts the 1s. An alternative is

=Abs(Sum([InvoiceDate] Between Date()-29 And Date()-15))

6. ## Re: Sum IIf (2002 SP-2)

The essential point was not the inclusion or omission of brackets, but the = you had in = Between. That didn't belong there.

7. ## Re: Sum IIf (2002 SP-2)

Well now, that's just absurd! I hadn't thought of it that way but, now that you've kindly explained it, it makes all the sense in the world.
BTW, "=" wasn't in there in the beginning, but after a while I just started throwing things at the screen to see if something would stick!
Frustration: the mother of random expression.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•