#Error in query results using Public Function

[sbowman]

I have a very complex query with about 50 fields - 10 fields are
results of different public functions. One in particular is giving me
trouble by returning a bunch of #Error results. It returns some valid
results, but mainly the #Errors. Here's the function:

Public Function TechAssigned(Dept, LanAdmin, Tech_1, Tech_2, Tech_3,
Tech_4, Tech_5 As String) As String

If Right(Dept, 1) = "0" And LanAdmin <> "" Then
TechAssigned = LanAdmin
ElseIf Right(Dept, 1) = "1" And Tech_1 <> "" Then
TechAssigned = Tech_1
ElseIf Right(Dept, 1) = "2" And Tech_2 <> "" Then
TechAssigned = Tech_2
ElseIf Right(Dept, 1) = "3" And Tech_3 <> "" Then
TechAssigned = Tech_3
ElseIf Right(Dept, 1) = "4" And Tech_4 <> "" Then
TechAssigned = Tech_4
ElseIf Right(Dept, 1) = "5" And Tech_5 <> "" Then
TechAssigned = Tech_5
Else
TechAssigned = "Not Specified"
End If

End Function

And here's the way I'm calling it in the query design view:
TechName:
TechAssigned([Department],[LanAdmin],[Tech_1],[Tech_2],[Tech_3],[Tech_4],[Tech_5])

HELP!! This is driving me nuts!

Thanks
Shelley

 

[sbowman]

Nevermind I figured it out...even though I was using <> "" as a
parameter, Access still errored out when it encountered a null value. I
ran a series of update queries to change the nulls to "Not Specified."
Works fine now...

Thanks!
Shelley

 

[Brendan Reynolds]

Probably Null values in one or more of the fields. For example, if Dept
begins with '0' and LanAdmin is Null, the code will attempt to assign Null
as the return value, which will raise an error because the return type is
declared as String and you can't assign a Null value to a String.

You can either deal with the possibility of Null values within the function,
or coerce the values to Strings when passing them to the function.

Check the help topics on 'NZ Function' and 'IsNull Function', and perhaps
also check out Allen Browne's articles on Null values and how to deal with
them at the following URL ...

http://allenbrowne.com/tips.html