Simple WHERE statement help...

[woodman650]

My code abilities are not the best yet...

but I've got this:

="$ " & Sum(tblFinance!Amount WHERE tblFinance.Type="Revenue")

It doesn't work. I'm just trying to display all of the revenues from a table in a sum command. Anyone know how I might be able to fix syntax? thanks!

 

[pbaldy]

You could try a DSum (more info in VBA Help).

 

[Moniker]

Type is all wrong there. I'm assuming Type is a field name. Well, the word Type is also a VB Keyword, and in VB, there's no such type as "Finance". Also, a straight "SUM" function can't include criteria (the WHERE statement). Use the DSum suggested before. It will look something like this (change your variable/table names as necessary):

=DSum("[Amount]","YOUR_TABLE_NAME_HERE","[Type]='Revenue'")

Even better, just include the currency formatting in there, like this:

=Format(DSum("[Amount]","YOUR_TABLE_NAME_HERE","[Type]='Revenue'"),"$0.00")

~Moniker

 

[woodman650]

oh cool. thanks Moniker. is there a way to invert the value in that statement? (make positive numbers negative, etc)? Just a formatting trick would do it I'm sure.

 

[Moniker]

That's just basic math. To make a positive negative or a negative positive, just multiply it by -1. As in:

-5 * -1 = 5

5 * -1 = -5

~Moniker