How to test for "#Error" condition in calculated field in a query

D

Douglas J. Steele

You've declared the parameter of the function to be a string. Strings cannot
be Null: the only data type that can have a value of Null is a Variant.
That's what's causing the error when you pass Null to the function.

Change it to

Public Function EmployeeNameFormat2(EmployeeNum As Variant) As String
 
S

Steve Schapel

Ivan,

You are getting an error because you are declaring the EmployeeNum
variable in your function as a String, which is not the case if it is
null. There are a number of ways to solve it. One would be to put your
calculated field in the query like this... (note, no =)
Expr: EmployeeNameFormat2(Nz([PrevField],""))

A few other comments about your function code... I notice you have not
catered to the situation of the emFirstName field being Null or
zero-length, nor have you catered to the option of emLastName field
being Null.
 
I

Ivan Starr

Hello, thank you for helping.

I have a Calculated field in a query that refers to another field in the
query that may or may not be NULL, or uninitialized or whatever. The
calculated field is of the form Expr:=EmployeeNameFormat2(PrevField).

When

Public Function EmployeeNameFormat2(EmployeeNum As String) As String
On Error GoTo Err
If IsNull(EmployeeNum) = True Then
EmployeeNameFormat2 = ""
GoTo Finish
End If
Dim rstEm As Recordset
Set rstEm = CurrentDb.OpenRecordset("dbo_EM")
rstEm.FindFirst ("emEmployee = '" + EmployeeNum + "'")
If rstEm!emLastName = "" Then
EmployeeNameFormat2 = ""
GoTo Finish
End If
EmployeeNameFormat2 = Mid(rstEm!emFirstName, 1, 1) + ". " + rstEm!emLastName

GoTo Finish
Err:
EmployeeNameFormat2 = ""

Finish:
rstEm.Close
End Function

I want the function to return "" when there is no data in field [PrevField],
but IsNull does not seem to work, nor does ="".

I have to keep "#Error" from appearing in the query or the report that is
based on it, instead
 
Top