Code to put Now() in Null fields? 1

[misssoysauce]

I have a report that calculates charges based on the amount of time a piece of equipment was on hire. This works by subtracting an on-hire time from an off-hire time to get an interval. Of course, a bug comes up whenever a job is still on-hire, i.e. no off-hire time is entered in the database.

So I would like to create some code that would replace the current date and time as the off-hire time for records with Null values, thus giving me an up-to-the minute cost for the rental, and eliminating the bugs.

The code below works when an off-hire time IS given, but returns 'Null', not the current time and date, when Null. How do I fix this?

Public Function SetOff(timeoff)

If timeoff = Null Then
OT = Now()
Else
OT = timeoff
End If
SetOff = OT
End Function

Many thanks in advance for your advice/help!!

 

[TonyJollans]

Hi misssoysauce,

You can't check for null like that - you must use
[blue][tt] If IsNull(timeoff) Then[/tt][/blue]

But better, probably, to use the Nz Function:
[blue][tt] SetOff = Nz(timeoff,Now())[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[misssoysauce]

Tony you are brilliant! It might not have been too difficult, but I couldn't find anything about it in any help references. Cheers!