# Thread: Getting #Error in a Form's Text Box

1. Registered User
Join Date
Oct 2009
Posts
204

## Unanswered: Getting #Error in a Form's Text Box

Hello,

I have 3 unbound text boxes I use to calculate days between fields, and two of them are getting errors, and I have no idea why...Here are the fields that are calculated from:

[NextAvail]
[PDCArrival]
[CompDate]

The first and third fields listed below are getting the error, the middle one is fine. I've tried several variations of this, and I'm not sure what I can do to have these fields show up blank if the return value is nothing. Basically, if [CompDate] is empty, I get the error. If there is a date in that field, it calculates out correctly.

Field 1:
=IIf((IIf([CompDate]<>"",workingdays([PDCArrival],[CompDate]),""))>=1,(IIf([CompDate]<>"",workingdays([PDCArrival],[CompDate]),"")),"0.5")

Field 2:
=IIf([CompDate]=Null,"",DateDiff("d",[NextAvail],[CompDate]))

Field 3:
=IIf([CompDate]=Null,"",workingdays([NextAvail],[CompDate]))

Here is the coding for function workingdays:
Code:
```Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
If EndDate >= StartDate Then

intCount = 0
Do While StartDate < EndDate
StartDate = StartDate + 1
'         If Weekday(StartDate, vbMonday) <= 5 Then
'-- Use the following code if you have a "Holiday" table
If Weekday(StartDate, vbMonday) <= 5 And _
IsNull(DLookup("[Holiday]", "tbl_Holidays", _
"[HolDate] = " & Format(StartDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then

intCount = intCount + 1
End If
Loop
WorkingDays = intCount
Else
WorkingDays = -1  '-- To show an error
End If
Else
WorkingDays = -1  '-- To show an error
End If

exit_workingDays:
Exit Function

err_workingDays:
MsgBox "Error No:    " & Err.Number & vbCr & _
"Description: " & Err.Description
Resume exit_workingDays

End Function```
Does anyone know what might be causing this error to appear, and how I might get rid of it?

2. Registered User
Join Date
May 2005
Location
Nevada, USA
Posts
2,888
Provided Answers: 6
Nothing is ever equal to Null. You'd want to use the IsNull() function. In fact, I'd probably use IsDate():

IIf(IsDate(CompDate), YourFunction, "")

The error is likely coming from Nulls being passed to the function, which isn't written to handle them.

3. Registered User
Join Date
Oct 2009
Posts
204
Thank you, for some reason the IsDate function got rid of the calculation and gave me only errors, but the IsNull worked perfectly.

4. Registered User
Join Date
May 2005
Location
Nevada, USA
Posts
2,888
Provided Answers: 6
Glad it helped. The logic was reversed with the IsDate() function. Your function would have been in the True argument of the IIf() instead of the False argument as you have it with the Null test.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•