Selection

[macca]

I have part of a query that checks a string and returns a 2 letter code. the string is 9 characters long and consists of 1's and 0's, but it is picking the wrong 2 letter code

String Code
100000000 AC
010000000 AR
001000000 CD
000100000 MC
000010000 MR
000001000 PLS
000000100 PS
000000010 SBP
000000001 TQ


The expression is as follows:

VALDISP2: IIf([DIS2]>="100000000
","AC",IIf([DIS2]>="010000000
","AR",IIf([DIS2]>="001000000
","CD",IIf([DIS2]>="000100000
","MC",IIf([DIS2]>="000010000
","MR",IIf([DIS2]>="000001000
","PLS",IIf([DIS2]>="000000100
","PS",IIf([DIS2]>="000000010
","SBP",IIf([DIS2]>="000000001
","TQ","")))))))))

However the string 000000001 is picked up as "" and not "TQ"

 

[ScottGem]

Using a nested IIF like that can be hard to to debug. I would suggest using a Custom Function instead:

Public Function ConvertBin(strBin As String) As String

SELECT Case strBin
     Case >="100000000",
          ConvertBin = "AC"
     
     Case >="010000000"
          ConvertBin = "AR",

etc.

End Select
End Function

You can then step thru the code and see what happens.