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,492 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access query 16 62
How to create and use encrypted columns in SQL 2005? 15 64
Moving database to a shared server 7 37
Access 2003 query, remove dupes 17 26
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 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