Help with subtracting dates!!! (1 Viewer)

[Johnnyw]

I have a form which logs requests for assistance. Some requests do not get closed for a long time or never closed at all. On the form I have a Date Open field and a Date Closed field. I want a text box to calculate how many days a request was open for which is easy, BUT if there is no close date then the answer would be blank. I want the box to subtract the Date Open from todays date "Now()" if the Date Closed is Null. Any help is very much appreciated!!

Thanks

 

[axa]

a simple answer to this is to use the DateDiff() function (as you propably would expect) along with that nice nz() function.

If you have not used nz() before, then its purpose is to make sure that if the givien argument is a Null, then a non-zero value is returned (and you can optionaly specify an alternative value to use if a Null is encountred)

For your purposes the following will do exactly what you want.

lngDays = DateDiff("d",txtDateOpen,nz(txtDateClosed,Now()))


hope that helps.

axa

 

[Johnnyw]

I put the function in the control source for a text box but it did not work. This is exactly how it looked.

IngDays=DateDiff("d",Date Open,nz(Date Closed,Now()))

I tried to eliminate the space between Date Open and Date Closed but it didnt work. The text box just says #Name?

Is there something I'm missing?

 

[axa]

ah...

if the form you are using is a DataBound form, then set result textbox's ControlCource to:-

=DateDiff("d",[Date Open],nz([Date Closed],Now()))


Note the use of [...] to enclose the field names, as you seem to have field names with spaces in.

As it is to be used in a textboxes ControlSource there does not need to be anything to the left of the equals sign (i wrote it in a VB module where i wanted to store the result in a variable, hence lngDays=...)


hope thats sorted it out for you.
axa

 

[Johnnyw]

Thanks a lot!!! It works perfectly!!

Thanks,
Johnnyw