?
Solved

Syntax in MS ACCESS SWITCH() when coulumn is NULL

Posted on 2010-08-24
10
Medium Priority
?
812 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 840 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 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 160 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

777 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