How to enter a derived column in a SQL Server query being built using Access that provides if/then/else functionality

I need to enter an expression for a derived field in a view. The view is being defined using Access 2002 connected to a SQL Server database. I want to test a numeric value and, if greater than 0, set the column value to 'Yes', otherwise set it to 'No'.

I have tried various verions of 'if' and 'iif' and I keep getting errors.

Kevin
LVL 81
zorvek (Kevin Jones)ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
in the view you can use the Case When function

Simple CASE function:

CASE WHEN yourfield >0 THEN 'Yes'
   ELSE 'No'
END

Alan



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try like:

Select IIF(mynumericfield > 0, 'Yes' , 'No') As YesOrNot From Mytable

?
0
Alan WarrenApplications DeveloperCommented:


hehe,

get an error saying query designer does not support case statement, but then it runs fine, hmmm.


SELECT     CASE WHEN ID > 0 THEN 'Yes' ELSE 'No' END AS 'colName'
FROM         dbo.tbl_YourName
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Eric FlammOwnerCommented:
SQL Server doesn't support an IIF function directly in a view. You could create a function to do what you want. In SQL Server, this would be a UDF, but i would build it in Access. in a module, just type:

Function Positive(myNum as integer) as String
   Positive="No"
   if mynum>0 then
      positive="Yes"
   end if
end function

Then, in your view, just use Positive([Numeric Field To Test]) as your derived field.

-ef
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>SQL Server doesn't support an IIF function directly in a view.
Ya.. use CASE instead of IIF in SQL Server, while in Access IIF is still working ;-)
0
zorvek (Kevin Jones)ConsultantAuthor Commented:
Thanks guys. I'm not an expert yet at SQL and I'm trying to use the graphical tools in Access to get the job done. Everything was working fine until I started trying to cram this into the column part of the designer. Some days I just want to throw Access out the damn window!

The CASE worked fine once I got over the fact that dsigner was not going to help me.

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.