From Acess IIF to SQL Server CASE, anyone, please?

Hello again experts.

can you please help to convert this IIF statement that I am current using in MS Access to MS SQL Server?

IIF(Inactive_Has_Clients,2,1)

Thanks for your assistance
simflexAsked:
Who is Participating?
 
MNelson831Connect With a Mentor Commented:
No it isn't.  Try this:

Select Case when  Inactive_Has_Clients <> 0 Then 2 Else 1 End
0
 
MNelson831Commented:
IIF(Inactive_Has_Clients,2,1)

Select Case when Inactive_Has_Clients then 2 else 1 end
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one out:

SELECT CASE WHEN Inactive_Has_Clients = 2 THEN 1 ELSE NULL END AS urcolumnname
FROM urtable
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
simflexAuthor Commented:
Ok, So I wasn't really that crazy because I tried both and none worked.

None worked meaning that normally, I get the form field populated with values from the IIF() function but it didn't work when I converted to sql an hour ago.

I will try each of them again and post back.

Many thanks to both of you for the prompt responses.
0
 
MNelson831Commented:
Is Inactive_Has_Clients a boolean expression?  It has to be if your IIF statement works.  The code you posted reads:

If the value of Inactive_Has_Clients is true then return 2 otherwise return 1

Is that what you intended?
0
 
simflexAuthor Commented:
Yes, Inactive_Has_Clients is a boolean (bit) data type in sql server
and yes
If the value of Inactive_Has_Clients is true then return 2 otherwise return 1

Those are correct but here is the error I am getting:

An expression of non-boolean type specified in a context where a condition is expected, near 'then'

I have no clue what that means. Isn't bit data type a bolean?
0
 
simflexAuthor Commented:
still getting this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'when'

Here is more code maybe my fault as I didn't give enough code. I didn't anticipate it being a challenge; that's why I gave little code.

Here is the entire code and lots of thanks for your help.


SQL	= "select commonname, Inactive_Has_Clients from tblUsers" _
        & " where Code = 'Mgr' " _
        & " and ( active=True OR Inactive_Has_Clients = True ) " _
        & " order by IIF(Inactive_Has_Clients,2,1), commonname"

Open in new window

0
 
simflexAuthor Commented:
Ok, I got it working.

You were right on track with this:

Select Case when  Inactive_Has_Clients <> 0 Then 2 Else 1 End

I made some slight changes to this:

(Case when  Inactive_Has_Clients <> 0 Then 2 Else 1 End)

and it is working now.
It is amazing how brackets could make such a difference.

Thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.