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

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
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
 
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.
 
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
 
Back
Top