I build simple Excel queries through Excel's Microsoft Query and send
them to clients. They operate off a SQL Server database.

Is there a way to use the IIf function or Case When statement in one of
these .dqy queries? When I try to use IIf, MS Query doesn't complain
about IIf, but I can't get past an "Incorrect syntax near '=' message
when I try to run the query.

Here's part of my query:

Sum(IIf(CRType='W', -CRAmount, CRAmount))

I've also tried without success the following:

Sum(Case When CRType="W" Then -CRAmount Else CRAmount End)

Any ideas? Is it even possible? This needs to be done in Microsoft Query
and saved as a .dqy file. I can do this using Access or VBA in Excel, so
I am not looking for that type of solution.

Thanks for looking.

Oh, you were so close. Here's the right way to write the Case statement:

Sum(Case CRTYPE when "W" then -CRamount Else CRamount End)

"steveh" wrote:

I build simple Excel queries through Excel's Microsoft Query and send
them to clients. They operate off a SQL Server database.

Is there a way to use the IIf function or Case When statement in one of
these .dqy queries? When I try to use IIf, MS Query doesn't complain
about IIf, but I can't get past an "Incorrect syntax near '=' message
when I try to run the query.

Here's part of my query:

Sum(IIf(CRType='W', -CRAmount, CRAmount))

I've also tried without success the following:

Sum(Case When CRType="W" Then -CRAmount Else CRAmount End)

Any ideas? Is it even possible? This needs to be done in Microsoft Query
and saved as a .dqy file. I can do this using Access or VBA in Excel, so
I am not looking for that type of solution.

Thanks for looking.

Thanks, Bob. Actually either one works, but I had to edit the SQL and
couldn't do it "graphically." Also, I mistyped -- had to use single
quotes instead of double quotes around the W. I like your syntax better,
though.

But an odd thing happens. It takes out all the aliases in my query, and
I don't have any label for the result. If I type in:

Sum(Case CRTYPE when 'W' then -CRamount Else CRamount End) AS Balance

When I run the query and view the SQL, it shows

Sum(Case CRTYPE when 'W' then -CRamount Else CRamount End)

(AS Balance is missing.) Any suggestions?


BobT wrote:
Oh, you were so close. Here's the right way to write the Case statement:

Sum(Case CRTYPE when "W" then -CRamount Else CRamount End)

"steveh" wrote:

I build simple Excel queries through Excel's Microsoft Query and send
them to clients. They operate off a SQL Server database.

Is there a way to use the IIf function or Case When statement in one of
these .dqy queries? When I try to use IIf, MS Query doesn't complain
about IIf, but I can't get past an "Incorrect syntax near '=' message
when I try to run the query.

Here's part of my query:

Sum(IIf(CRType='W', -CRAmount, CRAmount))

I've also tried without success the following:

Sum(Case When CRType="W" Then -CRAmount Else CRAmount End)

Any ideas? Is it even possible? This needs to be done in Microsoft Query
and saved as a .dqy file. I can do this using Access or VBA in Excel, so
I am not looking for that type of solution.

Thanks for looking.