AggregateOfMixedDataTypes error

I have an expression that returns Invoice amounts for the current
month. I try to Sum this though and get the aggregate of mixed data
types error. I've tried to convert the dates, but then get the 'trying
to Sum on data types other than numeric'. Can anyone help me figure
out how I can sum this column? Thank you. This is in Visual Studio on
SQL Server.
= iif(month(InvoiceDate)= month(getdate()) AND year(InvoiceDate)=
year(getdate()), InvoiceAmount, 0)
On Thu, 28 Feb 2008 09:44:32 -0800 (PST), p-rat <osupratt@.yahoo.com>
wrote:

>I have an expression that returns Invoice amounts for the current
>month. I try to Sum this though and get the aggregate of mixed data
>types error. I've tried to convert the dates, but then get the 'trying
>to Sum on data types other than numeric'. Can anyone help me figure
>out how I can sum this column? Thank you. This is in Visual Studio on
>SQL Server.
>= iif(month(InvoiceDate)= month(getdate()) AND year(InvoiceDate)=
>year(getdate()), InvoiceAmount, 0)
This looks like a mix of Access (iif) and SQL Server (getdate()). You
might want to explain that, and show the entire SELECT with the SUM.
In SQL Server I would write this:
CASE WHEN month(invoice_date) = month(getdate())
AND year(invoice_date) = year(getdate())
THEN InvoiceAmount
ELSE 0
END
Note that they type of the constant 0 has to be compatible with the
type of the column InvoiceAmount, and InvoiceAmount has to be a number
that can be SUMmed.
Roy Harvey
Beacon Falls, CT