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
1,493 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 400 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 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 100 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
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.

 
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 52

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

622 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