Invalid use of Null - (when missing the end date in a range)

[kmacd6951]

I was using the DateDiff function in one of my queries (as below). This would work for me in situations where there was no SWDATERESOLVED yet. It would just leave the CRT blank.

CRT: DateDiff("d",SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED])

But, because I need to EXCLUDE weekends, I needed to use code rather than the DateDiff function.
So here is my expression in the query:

CRT: GetWorkDays(SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED])

and this is the code:

Option Compare Database
Option Explicit


Public Function GetWorkDays(sStartDate, sEndDate) As Long
  Dim iDays As Long
  Dim iWorkDays As Long
  Dim sDay
  Dim i As Long
  
  iDays = DateDiff("d", sStartDate, sEndDate)
  
  iWorkDays = 0
  
  For i = 0 To iDays
    'First day of the week is sunday
    sDay = Weekday(DateAdd("d", i, sStartDate))
    If sDay <> 1 And sDay <> 7 Then
      iWorkDays = iWorkDays + 1
    End If
  Next
  GetWorkDays = iWorkDays
End Function

However, if there is no EndDate, the query bombs on me.
I get a

Microsoft Visual Basic pop-up Error:
Run-time error ‘94’:
Invalid use of Null

If I click on Debug, it takes me to this line of the code:

 iDays = DateDiff("d", sStartDate, sEndDate)

So I assume it is not knowing what to do when there is no EndDate. Not sure what the difference is in having my DateDiff in the Module vs directly in the query... but it is. How do I rectify this so that I can get a blank CRT when the SWDATERESOLVED is blank?

Thanks
Katie

 

[Joe4]

Re: Invalid use of Null - (when missing the end date in a ra

How about adding an IF statement to your function to check for an end date, and if one isn't there return nothing? If you want to return a blank instead of the number zero, you probably will have to dimension GetWorKDays as Variant instead of Long. Try this:

Public Function GetWorkDays(sStartDate, sEndDate) As Variant
  Dim iDays As Long
  Dim iWorkDays As Long
  Dim sDay
  Dim i As Long
  
  If sEndDate > 0 Then
    iDays = DateDiff("d", sStartDate, sEndDate)
    
    iWorkDays = 0
    
    For i = 0 To iDays
      'First day of the week is sunday
      sDay = Weekday(DateAdd("d", i, sStartDate))
      If sDay <> 1 And sDay <> 7 Then
        iWorkDays = iWorkDays + 1
      End If
    Next
    GetWorkDays = iWorkDays
  Else
    GetWorkDays = ""
  End If

End Function

 

[kmacd6951]

Re: Invalid use of Null - (when missing the end date in a ra

Tried what you suggested, as that makes sense. Now I am getting a different error though...
So my code:

Option Compare Database
Option Explicit


Public Function GetWorkDays(sStartDate, sEndDate) As Variant
  Dim iDays As Long
  Dim iWorkDays As Long
  Dim sDay
  Dim i As Long
  
  If sEndDate > 0 Then
    iDays = DateDiff("d", sStartDate, sEndDate)
  
    iWorkDays = 0
  
    For i = 0 To iDays
     'First day of the week is sunday
      sDay = Weekday(DateAdd("d", i, sStartDate))
        If sDay <> 1 And sDay <> 7 Then
         iWorkDays = iWorkDays + 1
     End If
    Next
  GetWorkDays = iWorkDays
 Else
  GetWorkDays = " "
 End If
End Function

In my query:
CRT: GetWorkDays(SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED])

When I run this I get this error:
Ambiguous name. in query expression
'GetWorkDays(SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED])'.

 

[Joe4]

Re: Invalid use of Null - (when missing the end date in a ra

It may be a problem with how you are calling the variables. You may want to try different combination of with and without brakcets.

 

[Joe4]

Re: Invalid use of Null - (when missing the end date in a ra

You said that your problem is when you don't have an end date. When this is the case, what is the value of [SWDATERESOLVED]? Is it blank, null, or error? This may be causing the problem.

 

[kmacd6951]

Re: Invalid use of Null - (when missing the end date in a ra

If there is no end date, that field is blank.

 

[Joe4]

Re: Invalid use of Null - (when missing the end date in a ra

I don't know if this will help, but you can try changing:

If sEndDate > 0 Then

to

If Len(sEndDate) > 0 Then

You may also need to flag the sEndDate argument as optional.

 

[kmacd6951]

Re: Invalid use of Null - (when missing the end date in a ra

Think I got it now... Monday morning my mind is a little better than Friday afternoon!

I had saved a copy of my old Module, so I had two module's with the 'GetWorkDays' function. I think that was confusing Access

Thank you so much for your help!