Solved

IF condition in Select Query

Posted on 2004-09-24
7
1,883 Views
Last Modified: 2008-02-01
I have a table like this

SerialNumber    MyDecision    FathersDecision       GrandFatherDecesion
-----------------------------------------------------------------------------------
1                                                                       GoToSchool
2                                             GoToNeighbors    GoToColony
3                      GoToFriends     GoToPeople         GoToAll
4                                              EatEnough          EatWell

I want to make a query that selects all of the above 4 fields as they are right now and i want to add another field that id called as SuperDecesion, it should have MyDecesion, if it is blank then, FathersDecision, if FathersDecision is also null then it should contain GrandFatherDecesion, in short the 5thcolumn should look like this in the aboce specific example

SuperDecesion
-----------------
GoToSchool
GoToNeighbors
GoToFeiends
EatEnough

can some one make exact query, i know it will have IF condition in query but i failed to make it. Please help ASAP.
0
Comment
Question by:shafiqrao
  • 4
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Nestorio earned 101 total points
ID: 12146528
Try:

Select *, Nz(MyDecision, Nz(FathersDecision,GrandFathersDecision)) as SuperDecision From table
0
 

Author Comment

by:shafiqrao
ID: 12146635
worked great, thanks.
0
 

Author Comment

by:shafiqrao
ID: 12146723
how to trim the blank spaces, i have fileds that have data like '    ' this is not treated as NULL so what should i modify in the query ?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 16

Expert Comment

by:Nestorio
ID: 12146845
Try this:
Select *, iif(nz(trim(mydecision))<>"", mydecision, iif(nz(trim(fathersdecision))<>"", fathersdecision, grandfathersdecision)) as SuperDecision From table
0
 

Author Comment

by:shafiqrao
ID: 12146963
for me

Select *, iif(nz(trim(mydecision))<>"", mydecision, iif(nz(trim(fathersdecision))<>"", fathersdecision, grandfathersdecision)) as SuperDecision From table
 
this is not working, but if you eliminate {as SuperDecision} from the query then it works fine and names the field as Expr1000, if i donot eliminate it then it asks me value of SuperDecision.Expr1000

is it working with you ?
0
 

Author Comment

by:shafiqrao
ID: 12146984
it worked, sorry, my own mistake, thanks for co-operation.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12147048
You are welcome
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now