Null fields

[TCARPENTER]

I have written a function for reporting when a field is blank or null, the field's value prints "*N/S". The function worked fine until I ran a report containing phone numbers. The field containing the phone number is defined as text and has no formatting. Here is my code:

Public Function rptBlankFields(varFieldValue As Variant, Optional varNSValue) As Variant

On Error GoTo rptBlankFields_Error
  
  If IsMissing(varNSValue) Then
    varNSValue = "*** TBD ***"
  End If
    
  rptBlankFields = IIf(IsNull(varFieldValue) Or varFieldValue = varNSValue, "*N/S", varFieldValue)
  
rptBlankFields_Exit:
  Exit Function

rptBlankFields_Error:
  
  If Err.Number = 2427 Then
    ' varFieldValue has no value, might as well be null.
    '
    rptBlankFields = "*N/S"
    
  Else
    ' Some other error...
    Debug.Print "Error Number: " & Err.Number & vbCrLf & "Error Descr: " & Err.Description
    
  End If
  
  Resume rptBlankFields_Exit
  
End Function

The problem is it's always returning "*N/S". I did check for zero length strings but always get the error message "...has no value." What am I missing?

Thanks
Todd

 

[lupins46]

Works OK for me. Your problem must be to do with how/where you are calling it.

But I think you do need a test for "" (zero length string).

 

[TCARPENTER]

I hadn't thought of that, I use it as the control source for text box controls on reports. Where else could I use it?

 

[TCARPENTER]

And my control source looks like this:

=rptBlankFields([TL_MstrTools_ToolWeight])

[TL_MstrTools_ToolWeight] is a field from a table.