Solved

Syntax in MS ACCESS SWITCH() when coulumn is NULL

Posted on 2010-08-24
10
788 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 49

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

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.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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