Solved

Syntax in MS ACCESS SWITCH() when coulumn is NULL

Posted on 2010-08-24
10
810 Views
Last Modified: 2012-05-10
Hello Experts,
I'm getting an error on the column which has a Null value using SWITCH(). Tried to convert it but it seems i've used the wrong syntax. Please help...

Onmy statement below, the error i'm getting is for the last column output (T_Assessment_Score)
SELECT a.Assessment_Rec_ID,
            a.Year_Group,
            a.Surname,
            a.Firstname,
           a.DOB,
           a.Gender,
           a.Form,
           a.Form_Teacher,
           a.Assessment_Period,
           nz(a.Assessment_Score,"IS NULL") as ORIG_rawScore
           c.Category_Doc_ID,
           c.Category_ID,
           SWITCH(
           cstr(a.Assessment_Score)  > 36, "88888",
           cstr(a.Assessment_Score)  < 0, "88888",
           cstr(nz(a.Assessment_Score,"IS NULL")) = 'IS NULL', "99999",
           True, a.Assessment_Score
           ) as T_Assessment_Score  
FROM T_ASSESMENT_MONITORING AS a, T100_MON_CAT_DOC AS c
WHERE a.Assessment_period='2010 Term 3'
And a.Assessment_Category='101'
And a.Form='4/5 M'
And a.Assessment_Category_Doc=c.Category_Document_Description;
 
0
Comment
Question by:jsuanque
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 

Author Comment

by:jsuanque
ID: 33518045
Please note that  the column  (Assessment_Score)is a numeric data type.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33518083

what is nz?

Maybe try this.

IFNULL(cstr(nz(a.Assessment_Score)),"99999")
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33518090

do a simple query to test.


Select IFNULL(a.Assessment_Score,'99999') from yourtable;

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 8

Expert Comment

by:kingjely
ID: 33518095

with CSRT

Select IFNULL(cstr(a.Assessment_Score,'99999')) from yourtable;
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33518117
Sorry my bad that is mysql syntax.
Dufas
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33518126
This works for me

SWITCH(
           a.Assessment_Score&"" = "", "99999",
           a.Assessment_Score  > 36, "88888",
           a.Assessment_Score  < 0, "88888",
           True, a.Assessment_Score
           ) AS T_Assessment_Score
0
 

Author Comment

by:jsuanque
ID: 33518253
Hello Cyberkiwi,
Unfortunately it still has an error.(for tjust the particualr record which has a NULL value)

Hello Kingiely,
Sorry that doesn't work as well. Also note the error only occurs in SWITCH()
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 210 total points
ID: 33518669
Can you post a mdb/accdb with just the one table?
I tried it in Access 2007, but I suspect it works in 2003 as well.  I had numbers and null fitting each of the 4 switch cases.
Try just this [verbatim] - the Switch for null test (first one below) must appear before all others.

SWITCH(
           a.Assessment_Score&"" = "", "99999",
           a.Assessment_Score  > 36, "88888",
           a.Assessment_Score  < 0, "88888",
           True, a.Assessment_Score
           ) AS T_Assessment_Score
FROM T_ASSESMENT_MONITORING AS a, T100_MON_CAT_DOC AS c
WHERE a.Assessment_period='2010 Term 3'
And a.Assessment_Category='101'
And a.Form='4/5 M'
And a.Assessment_Category_Doc=c.Category_Document_Description;
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 40 total points
ID: 33518853
Tto return strings only, try with:

         SWITCH(
           a.Assessment_Score > 36, "88888",
           a.Assessment_Score < 0, "88888",
           IsNull(a.Assessment_Score), "99999",
           True, CStr(a.Assessment_Score)
           ) as T_Assessment_Score

/gustav
0
 

Author Closing Comment

by:jsuanque
ID: 33528748
Hello Cactus_Data,
Stiil got an error on column rows which returned nulls. But nevertheless thanks heaps for your suggestion.

Hello Cyberkiwi...
For some reason (probably i might have added something i didn't realized when i tried your first suggestion yesterday)  it now works..
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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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