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?
 
Alan WarrenConnect With a Mentor Commented:
in the view you can use the Case When function

Simple CASE function:

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

Alan



0
 
Ryan ChongConnect With a Mentor Commented:
try like:

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

?
0
 
Alan WarrenCommented:


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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Eric FlammSenior ConsultantCommented:
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 ChongCommented:
>>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
All Courses

From novice to tech pro — start learning today.