Datediff, if field is blank

[Bjarni]

I am new to coding so I could use some help with the
datediff function in a case when field is blank.

I have a table with these fields:

CO #1 (plans Start) Date
CO Sign Off Date

In a query I have the following expresion:
CO Elapsed Time: GetElapsedDays([CO #1 (plans Start) Date]-
[CO Sign Off Date])

My Module contains:
Option Explicit

Function GetElapsedDays(interval)
Dim days As Long
days = Int(CSng(interval))
GetElapsedDays = days & " Days "

End Function

this works fine when both CO #1 (plans Start) Date &
CO Sign Off Date have dates in them, otherwise my query
returns a Run time error #94, invalid use of Null. What
would be the correct way of handling the "if field is
blank" routine?

I know this is an easy one but I can not get it to work
correctly.......

TIA

Bjarni

 

[Cameron Sutherland]

try wrapping your routine in a date test something like
this:

If IsDate (DateVariableComingIn) then
<Do you work here>
Else
<Do something else like:>
Exit Function
msgbox "Bad.."
Goto ErrorFound
End If

-Cameron Sutherland

 

[Marshall Barton]

I am new to coding so I could use some help with the
datediff function in a case when field is blank.

I have a table with these fields:

CO #1 (plans Start) Date
CO Sign Off Date

In a query I have the following expresion:
CO Elapsed Time: GetElapsedDays([CO #1 (plans Start) Date]-
[CO Sign Off Date])

My Module contains:
Option Explicit

Function GetElapsedDays(interval)
Dim days As Long
days = Int(CSng(interval))
GetElapsedDays = days & " Days "

End Function

this works fine when both CO #1 (plans Start) Date &
CO Sign Off Date have dates in them, otherwise my query
returns a Run time error #94, invalid use of Null. What
would be the correct way of handling the "if field is
blank" routine?

No need for you to create a function. You can use the
built-in DateDiff function.

How you do it depends on what you want the result to be when
one of the fields is null. This will return a Null when one
of the fields is Null:

CO Elapsed Time: DateDiff("d ,[CO Sign Off Date], [CO #1
(plans Start) Date])

 

[Bjarni]

Thank you both for the answer, I went ahead and used
Marshalls answer:
CO Elapsed Time: DateDiff("d",[CO Sign Off Date], [CO #1

(plans Start) Date])

Works like a Charm!

Bjarni

-----Original Message-----

I am new to coding so I could use some help with the
datediff function in a case when field is blank.

I have a table with these fields:

CO #1 (plans Start) Date
CO Sign Off Date

In a query I have the following expresion:
CO Elapsed Time: GetElapsedDays([CO #1 (plans Start) Date]-
[CO Sign Off Date])

My Module contains:
Option Explicit

Function GetElapsedDays(interval)
Dim days As Long
days = Int(CSng(interval))
GetElapsedDays = days & " Days "

End Function

this works fine when both CO #1 (plans Start) Date &
CO Sign Off Date have dates in them, otherwise my query
returns a Run time error #94, invalid use of Null. What
would be the correct way of handling the "if field is
blank" routine?

No need for you to create a function. You can use the
built-in DateDiff function.

How you do it depends on what you want the result to be when
one of the fields is null. This will return a Null when one
of the fields is Null:

CO Elapsed Time: DateDiff("d ,[CO Sign Off Date], [CO #1
(plans Start) Date])