Function Returning #ERROR if Value is Null

[LarryDeLaruelle]

I wrote a function to group similar Diagnoses under common headings using a Select Case statement.&nbsp;&nbsp;The Function is called from a Query and if the value I pass is null the resulting query display shows the #ERROR.&nbsp;&nbsp;Any other value passed to the Function produces the expected result.<br><br>The field I am using is Data Type Text and I am passing the value as a String.&nbsp;&nbsp;I tested to make sure that the field contained nulls by using the &quot;Is Null&quot; criteria in a Query.<br><br>A portion of my code:<br><br>Public Function Diagnosis(DiagnosisNum as String) As String<br>&nbsp;&nbsp;&nbsp;Select Case DiagnosisNum<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;315.00&quot;, etc<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Diagnosis = &quot;Learning Disorders&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;. . . <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Diagnosis = &quot;Blank or Miscoded&quot;<br>&nbsp;&nbsp;&nbsp;End Select<br><br>I assumed (big mistake) that any value not specified in the Case statements, including Nulls and empty strings would return the Case Else value.<br><br>I tried trapping for Nulls at the begining of the Function with:<br>&nbsp;&nbsp;&nbsp;If DiagnosisNum Is Null Then<br>and received a Type Mismatch error when I run the query.<br><br>I also tried:<br>&nbsp;&nbsp;&nbsp;If Nz[DiagnosisNum]<br>and<br>&nbsp;&nbsp;&nbsp;If Is Null DiagnosisNum<br>and received a compile error on each.<br><br>And I tried:<br>&nbsp;&nbsp;&nbsp;If DiagnosisNum = &quot;&quot; or DiagnosisNum = &quot; &quot; Then<br>which had no effect; still returned #ERROR on Null values.<br><br>This is driving me nuts!!&nbsp;&nbsp;Any suggestions will be gratefully received.&nbsp;&nbsp;Thanks.<br><br> <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[LarryDeLaruelle]

I beat you to it.<br><br>When I pass the value as type Variant the function works fine.<br><br>Nulls sure are tricky critters.<br><br>I would be interested in any other ways that this problem could have been resolved.<br><br>Thanks. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[christheprogrammer]

You can easily strip out nulls and replace with &quot;&quot; by doing this before the case statement:<br><br>DiagnosisNum = Format(DiagnosisNum)<br><br>

 

[LarryDeLaruelle]

Chris:<br><br>Is that all I need:&nbsp;&nbsp;DiagnosisNum = Format(DiagnosisNum)?<br>Or is there more to it?<br><br>For now, passing the value as type Variant seems the most expedient way of getting the result I want, but your suggestion is certain to come in handy in other situations.<br><br>Thanks for your reply. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[Lightning]

Try the expression<br><br>If IsNull(DiagnosisNum) Then ...

 

[LarryDeLaruelle]

Thanks Lightening but that did not work.&nbsp;&nbsp;Although the syntax seems to compile correctly, I still get the #ERROR result when I pass the value as string. <p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br>

 

[WoodLark]

Try DiagnosisNum=DiagnosisNum & &quot;&quot; before you start testing<br>the value of DiagnosisNum.