Results 1 to 4 of 4
  1. #1
    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. #2
    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.
    Paul

  3. #3
    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. #4
    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.
    Paul

Posting Permissions

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