Calc months left with no negatives

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel - 08 Dec 2005 03:59 GMT

I'm using the following formula in a query to calculate the number of months
left on a lease based on a set end date and todays date:
MonthsLeft: DateDiff("m",Now(),[EndDate])

I dont want it to display negative numbers though for leases that have
finished.
How do i get it to show a "0" for dates that have already past or if the
number is going to show a negative

thanks
Daniel

Reply to this Message

Van T. Dinh - 08 Dec 2005 04:37 GMT

MonthsLeft: IIf( [EndDate] < Date(), 0, DateDiff("m", Date(), [EndDate]) )

Signature

HTH
Van T. Dinh
MVP (Access)

> I'm using the following formula in a query to calculate the number of
> months
[quoted text clipped - 8 lines]
> thanks
> Daniel

Reply to this Message