Access to SQL troubles

Michaelj42
Michaelj42 used Ask the Experts™
on
I'm trying to convert a query to SQL but I'm having trouble with two calculated fields:

[InvoiceAmount]+IIf(IsNull([InvoiceAmount2]),0,[InvoiceAmount2]) AS InvoiceTotal.
[ItemAmount]+IIf(IsNull([ItemAmount2]),0,[ItemAmount2]) AS ItemTotal  

became

dbo.Jobs.ItemAmount + IIf(ISNULL(dbo.Jobs.ItemAmount2,), 0, dbo.Jobs.ItemAmount2) AS ItemTotal
dbo.Jobs.InvoiceAmount + IIf(ISNULL(dbo.Jobs.InvoiceAmount2,), 0, dbo.Jobs.InvoiceAmount2) AS InvoiceTotal

but it errors near ")"
what would be the correct syntax? I know I need a 0 or a 1 at the end of my isnull statement but the IIF doesn't work either.

                     
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Instead of IIF you need to use CASE/WHEN statement.  The Isnull Statement doesnt' work the same was in Access.  It doesn't do a comparison, instead it returns a value instead of a null.


[InvoiceAmount]+IIf(IsNull([InvoiceAmount2]),0,[InvoiceAmount2]) AS InvoiceTotal.

Would Be:

COALESCE(InvoiceAmount2, 0)  AS ItemTotal
Correction:

[InvoiceAmount]+COALESCE(InvoiceAmount2, 0)  AS ItemTotal

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial