Calculating datediff using 'If' & 'Then' statements 1

[Knackers]

I have two date fields (startdate, leavedate) that I want to use in a Datediff calculation on a form. However, sometimes the leavedate field maybe empty (Null), so what I want to do is write an expression that check the leavedate to see if there is a value. Then if there is a value, use it in a Datediff calculation. If there is no value, I want it to use the Now() function to perform the calculation.

I have tried using the 'If' and 'Then' statements with very little luck. DOes anyone know of how it might work in this instance - or any other solution!
Thanks

 

[gwinn7]

Yes, very simple. Use the "Nz" function to eliminate NULLs.

Example:

Public Function ReturnTheDate() as Date
Dim dteStart as Date
dteStart = Nz(rc.Fields("Value&quot, Now())
End Function

The above function uses the Nz function to eliminate the null in the recordset field, "Value". If there is a null, the value returned is Now(). If there is a value in "Value", then the Value is returned.

Gary
gwinn7

 

[Knackers]

Thanks for your quick response Gary. I think that I am nearly there. I have copied the ReturnTheDate() function into a module and saved it.

I then made the contol for a box on the form
=DateDiff('m',[joindate],[ReturnTheDate()]) but it is giving me a #Name error.

How do I specify 'leavedate' in the expression
=DateDiff('m',[joindate],[leavedate]) to be the result of the ReturnTheDate() function?

Thanks a million

 

[braindead2]

wouldn't this accomplish what you desire

iif([leavedate] is null,DateDiff'm',[joindate],now(),DateDiff ('m',[joindate],[leavedate]) )

 

[gwinn7]

Brain, too long.

The "returnthedate" function was only an example of its application. You are getting the error probably because of the brackets surrounding the function name. Here is your code modified...

=DateDiff('m',[joindate],Nz([leavedate],Now()))

That should do it or close. You may have to tweak the Nz function with the brackets surrounding the leavedate to get it to work right, but I am not sure without trying out my own test.


Gary
gwinn7

 

[Knackers]

Gary,

Feeling a little stupid. Expression is fixed and all working now. Thanks for your help! You get my vote for tipmaster....

 

[gwinn7]

Thanks, just being as helpful as possible.

Gary
gwinn7

 

[PhilipSchroevers]

Gary,

ReturnTheDate doesn't return a date, it just sets the value of a local variable.

 

[gwinn7]

Yeah, but it will if you add the line...

ReturntheDate = dteStart

However, you are missing the point. The point was to provide a sample code showing how to use the Nz function.

Gary
gwinn7