none
Switch function not working RRS feed

  • Question

  • Hello there!

    I have a text fields in my table of size  1. They store a value 1, 0 or empty string. In my report I have used the following Switch statement but for all empty strings it displays N instead of " ". Can someone tell me what mistake I'm making.

    Here is my switch statement: =Nz(Switch([SW]=1,"y",[SW]=0,"n")," ")

    Joe

    Friday, January 27, 2017 3:07 PM

All replies

  • Your Nz checks whether Switch returns Null, which is never the case. You can use this instead:

    Switch(IsNull([sw]),"",[sw]=1,"y",[sw]=0,"n")

    or

    Switch(IsNull([sw]),Null,[sw]=1,"y",[sw]=0,"n")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 27, 2017 3:37 PM
  • Hi Joe,

    As Hans said, a Null value is not the same as an Empty String. You can try modifying your code as he suggested or also try other alternatives such as the following:

    =IIf([SW]=""," ",Choose([SW],"n","y"))

    =Switch([SW]=""," ",[SW]=1,"y",[SW]=0,"n")

    etc...

    Hope it helps...

    Friday, January 27, 2017 4:20 PM
  • Since my report is based on query, I modified my query and added IIf([SW]="1",”y”,IIf([SW]="0",”n”,"")) AS swa. When I run this simple SELECT query, swa column is empty for all rows. In fact it should show me y or n and a few empty values.
    Friday, January 27, 2017 5:59 PM
  • Since my report is based on query, I modified my query and added IIf([SW]="1",”y”,IIf([SW]="0",”n”,"")) AS swa. When I run this simple SELECT query, swa column is empty for all rows. In fact it should show me y or n and a few empty values.

    Hi,

    What is the data type of the field [SW]? 1 is not really the same as "1" but Access will sometimes make implicit conversions. If the field is Text, "1" would be appropriate. If it's a Number, then 1 would be more applicable. However, if it's a Number field, then you can't really have an Empty String in it.

    Just my 2 cents...

    Friday, January 27, 2017 6:07 PM