Getting #Error returned from Custom Function

[sbowman]

I have the following fields in a table:
Department - text
DateAssigned1 - Date
DateAssigned2 - Date
DateAssigned3 - Date
DateAssigned4 - Date
DateAssigned5 - Date

Where the records look like this:
Department-1 09/1/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006
Department-2 09/1/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006
Department-3 09/1/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006
Department-4 09/1/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006
Department-5 09/1/2006 9/5/2006 9/6/2006 9/7/2006 9/8/2006

I am trying to use the date that corresponds with the rightmost value
in Department (1-5). I am using the following custom function:

Public Function PFPM_WeeklyAssigned(Dept As String, Lan As Date, Tech1
As Date, Tech2 As Date, Tech3 As Date, Tech4 As Date, Tech5 As Date)
On error GoTo Err_Section
Dim DeptCheck As String
DeptCheck = Right(Dept, 1)

If DeptCheck = "0" Then
PFPM_WeeklyAssigned = Lan
ElseIf DeptCheck = "1" Then
PFPM_WeeklyAssigned = Tech1
ElseIf DeptCheck = "2" Then
PFPM_WeeklyAssigned = Tech2
ElseIf DeptCheck = "3" Then
PFPM_WeeklyAssigned = Tech3
ElseIf DeptCheck = "4" Then
PFPM_WeeklyAssigned = Tech4
ElseIf DeptCheck = "5" Then
PFPM_WeeklyAssigned = Tech5
End If

Err_Section:
Err.Clear
End Function

This function only returns the correct Date Value for a handful, I get
#Error for the rest. I tried to debug and I put a break at the
beginning of the function, but it seems to skip over the ones that
return #Error. WTF??

Thanks,
Shelley

 

[Douglas J. Steele]

Does every record always have all 5 date fields filled in? If not, the
problem is likely that you're trying to pass Null as a value, and Date
fields cannot accept Null values.

If you reported the error, rather than clearing it, you'd probably get
something useful to help solve the problem. Of course, if your database was
properly normalized, you likely wouldn't need the function.

 

[sbowman]

Thanks, the nulls were the problem. I set the nulls to 01/01/1900 and
it works fine now.

Shelley