Solved

IF condition in Select Query

Posted on 2004-09-24
7
1,886 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

840 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