Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-10-05
6
Medium Priority
?
1,495 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:zorvek (Kevin Jones)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 26

Accepted Solution

by:
Alan Warren earned 1600 total points
ID: 12233783
in the view you can use the Case When function

Simple CASE function:

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

Alan



0
 
LVL 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 400 total points
ID: 12233791
try like:

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

?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12233933


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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 8

Expert Comment

by:Eric Flamm
ID: 12234052
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
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 12234083
>>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
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 12234373
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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question