DateDiff in function 
Author Message
 DateDiff in function

I am trying to create a function that gives me the number of days between
two dates.  If there is not complete date I would like the function to
datediff using today's date as the completed date to give me the number of
days the object as been open.  However if I test for the completed date is
null or empty I get a type mismatch error. If I test for the completed date
as "0" then the code executes but I get a #error in the query for all the
entries that do not have a completed date.

Here is the code:

Function DaysOpen(accepted As Date, complete As Date) As Integer

Dim today As Date

today = Date

  If complete Is Not Null Then
    DaysOpen = DateDiff("d", accepted, complete)
  Else
    DaysOpen = DateDiff("d", accepted, today)
  End If

End Function

Any suggestions would be appreciated.  I can't seem to find any
documentation anywhere concerning testing empty date fields.

Sarah Burden
Reynolds & Reynolds



Sat, 13 Oct 2001 03:00:00 GMT  
 DateDiff in function
The "Is Not Null" reference is a SQL command.  Try using IsNull().  For
example:
    If IsNull(complete) then
        ...
    else
        ...
    end if

I hope that works for you.

Michael Kucan


Quote:
> I am trying to create a function that gives me the number of days between
> two dates.  If there is not complete date I would like the function to
> datediff using today's date as the completed date to give me the number of
> days the object as been open.  However if I test for the completed date is
> null or empty I get a type mismatch error. If I test for the completed
date
> as "0" then the code executes but I get a #error in the query for all the
> entries that do not have a completed date.

> Here is the code:

> Function DaysOpen(accepted As Date, complete As Date) As Integer

> Dim today As Date

> today = Date

>   If complete Is Not Null Then
>     DaysOpen = DateDiff("d", accepted, complete)
>   Else
>     DaysOpen = DateDiff("d", accepted, today)
>   End If

> End Function

> Any suggestions would be appreciated.  I can't seem to find any
> documentation anywhere concerning testing empty date fields.

> Sarah Burden
> Reynolds & Reynolds



Sat, 13 Oct 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help with DateDiff function

2. Function DateDiff results as condition

3. Excluding weekends from DateDiff Function

4. DateDiff-function

5. DateDiff Function

6. Negative values from DateDiff function

7. datediff function

8. Calculating Time Difference using DateDiff Function in VBScript

9. DateDiff function

10. About Datediff function

11. Using the DateDiff function

12. Help with the math in a datediff function

 

 
Powered by phpBB® Forum Software