[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

Syntax in MS ACCESS SWITCH() when coulumn is NULL

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
jsuanque
Asked:
jsuanque
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
jsuanqueAuthor Commented:
Please note that  the column  (Assessment_Score)is a numeric data type.
0
 
kingjelyCommented:

what is nz?

Maybe try this.

IFNULL(cstr(nz(a.Assessment_Score)),"99999")
0
 
kingjelyCommented:

do a simple query to test.


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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kingjelyCommented:

with CSRT

Select IFNULL(cstr(a.Assessment_Score,'99999')) from yourtable;
0
 
kingjelyCommented:
Sorry my bad that is mysql syntax.
Dufas
0
 
cyberkiwiCommented:
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
 
jsuanqueAuthor Commented:
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
 
cyberkiwiCommented:
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
 
Gustav BrockCIOCommented:
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
 
jsuanqueAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now