Solved

Syntax in MS ACCESS SWITCH() when coulumn is NULL

Posted on 2010-08-24
10
776 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
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…

911 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

23 Experts available now in Live!

Get 1:1 Help Now