Evaluating Null in a Case Function

[orcinus]

I have a case function which I want to return values in a query that reads a language type. Some of the language records will be null and for those, I want "BLANK" returned. When I use the below code, I don't get anything returned in the query field. All the other non null values appear without issue.

Any Suggestions? The null evaluation is at the end of the function.

Thanks!
Function fcnConvLang(Lang As Variant) As String


Select Case Lang
Case "CANTONESE"
fcnConvLang = "CHINESE"
Case "MANDARIN"
fcnConvLang = "CHINESE"
Case "ENGLISH"
fcnConvLang = "OTHER"
Case "JAPANESE,NIHONGO"
fcnConvLang = "OTHER"
Case Null
fcnConvLang = "BLANK"
End Select
End Function

[pbaldy]

The field may contain a zero length string, so try

Case ""

[orcinus]

The field is not a zero length string (I checked with what you suggested - no dice - I also made a query where the field criteria is null and it gives records)..

[pbaldy]

Hmm. Is it practical to let those get caught with an Else or are you using that for something else?

[orcinus]

I can use the Case Else and that does work. Curious though why 'Case Null' doesn't work? btw, also tried Case Is Null and Case IsNull(Lang). Neither of these worked.

[pbaldy]

I was surprised it didn't work too. If I get time I'll play with it, but at least you have something that works.

[orcinus]

Cool..thanks so much for your help!

[ssanfu]

Hi Paul & orcinus

I was curious also, so I tried to get the code to using NULL. No Luck. I found this in Help:

Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

So, even in the Select Case, comparing Null (in lang) to Case NULL will result in NULL (False).

I did come up with a couple of work arounds:

1) Test for NULL before the case statements:

Function fcnConvLang(Lang As Variant) As String
   If IsNull(Lang) Then
      fcnConvLang = "BLANK"
   End If
   
   Select Case Lang
      Case "CANTONESE"
         fcnConvLang = "CHINESE"
      Case "MANDARIN"
         fcnConvLang = "CHINESE"
      Case "ENGLISH"
         fcnConvLang = "OTHER"
      Case "JAPANESE,NIHONGO"
         fcnConvLang = "OTHER"
   End Select
   
End Function

2) Check for NULL in the calling statement and changing "Lang" from a Variant to a String:

txtReturn = fcnConvLang(nz(txtEntry, "Is_Null"))

Function fcnConvLang(Lang As String) As String
   
   Select Case Lang
      Case "CANTONESE"
         fcnConvLang = "CHINESE"
      Case "MANDARIN"
         fcnConvLang = "CHINESE"
      Case "ENGLISH"
         fcnConvLang = "OTHER"
      Case "JAPANESE,NIHONGO"
         fcnConvLang = "OTHER"
      Case "Is_Null"
         fcnConvLang = "BLANK"
      Case Else
         fcnConvLang = "BLANK"
   End Select
   
End Function

[orcinus]

Thanks, those "workarounds" are useful. Much appreciated..

Hi Paul & orcinus

I was curious also, so I tried to get the code to using NULL. No Luck. I found this in Help:

So, even in the Select Case, comparing Null (in lang) to Case NULL will result in NULL (False).

I did come up with a couple of work arounds:

1) Test for NULL before the case statements:

Function fcnConvLang(Lang As Variant) As String
  If IsNull(Lang) Then
      fcnConvLang = "BLANK"
   End If
   
   Select Case Lang
      Case "CANTONESE"
         fcnConvLang = "CHINESE"
      Case "MANDARIN"
         fcnConvLang = "CHINESE"
      Case "ENGLISH"
         fcnConvLang = "OTHER"
      Case "JAPANESE,NIHONGO"
         fcnConvLang = "OTHER"
   End Select
   
End Function

2) Check for NULL in the calling statement and changing "Lang" from a Variant to a String:

txtReturn = fcnConvLang(nz(txtEntry, "Is_Null"))

Function fcnConvLang(Lang As String) As String
   
   Select Case Lang
      Case "CANTONESE"
         fcnConvLang = "CHINESE"
      Case "MANDARIN"
         fcnConvLang = "CHINESE"
      Case "ENGLISH"
         fcnConvLang = "OTHER"
      Case "JAPANESE,NIHONGO"
         fcnConvLang = "OTHER"
      Case "Is_Null"
         fcnConvLang = "BLANK"
      Case Else
         fcnConvLang = "BLANK"
   End Select
   
End Function