Supress error on unbound control 1

[kwor]

I have a form with two bound controls - StarDate and EndDate.
Using code as per faq181-261, I use a third unbound control to display the number of working days between the dates. This works well.

However, if there are no dates in the two bound controls, the unbound, calculated control displays #Error.
I presume this is due to calculations involving zeros or nulls. Is there any way to suppress #Error being displayed?

 

[tobynegus]

within your code, get it to check if there is data in the two date fields, if not get it to return null

 

[kwor]

I put the following code at the start of the function DeltaDays.

If IsNull([StartDate]) Then
Me.WorkDays = Null
Exit Function
End If

However, the unbound control WorkDays still displays #Error

 

[TheAceMan1]

How are ya kwor . . .

... and what if the [blue]Endate[/blue] is Null?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kwor]

I tried that - same #Error displayed. I have attached the entire function code below.

In the source of the unbound control is the following code:
=DeltaDays([StartDate],[EndDate])

I do not understand why it is displaying #Error.
This is running under Access 2007 if that makes any difference.


Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

Dim dbs As Database
Dim rstHolidays As Recordset
Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

If IsNull([StartDate]) And IsNull([EndDate]) Then
DeltaDays = Null
Exit Function
End If

MyDate = Format(StartDate, "Short Date")

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

 

[dhookom]

You are sending in Nulls to a function that expects date values. You are also attempting to return a Null when the declaration suggests the function returns and integer

You must send true date values or change the code like:

Public Function DeltaDays(StartDate As Variant, _
            EndDate As Variant) As Variant
      If IsNull([StartDate]) Or IsNull([EndDate]) Then
          DeltaDays = Null
           Exit Function
      End If

Duane
Hook'D on Access
MS Access MVP

 

[kwor]

Fixed!
Thank you. I shall remember that.