Custom column using IF/AND

[analyst85]

Hello

I'm attempting to create a custom column based on the values of two existing columns (Score, Name) however I only get zeroes when using the below query and can't understand why?

if [Score = "3" and [Name] = "RT01" then "This service has alot of radios" else if [Score] = "3" and [Name] = "RT02" then "This service has less radios" else if [Score] = "3" and [Name] = "J01" then "This service has alot of jobs" else if [Score] = "3" and [Name] = "J02" then "This service has less jobs" else 0

[HotChilli]

So we can see that all rows are falling through to the 'else' clause and returning 0.

If you want to debug this write a new, simple custom column with a single test - if, then, else

and try to make that work.

for example: if [Score] = "3" then "success" else "fail"

----

Looking at your code, I can guess that it may return all "fail" - see if you can make it work

[mahoneypat · Accepted solution]

The first [Score  is missing a ] but i assume that is a type.  If the Score column is numeric you need just put the number without quotes  - [Score] = 3

Pat

[analyst85]

Thanks, the removal of quotes helped but I only get a succesful outcome if the first IF statement is met. However if the second statement is met it doesn't work and gives me zeroes still. Is there something I need to do to ensure each statement is checked?

[analyst85]

Nevermind it has actually worked, thanks so much!