Monthly Aging Buckets for query

[Abraxus]

I have a query that shows an aging for an employee's tenure:

0-12 Months
13-18
Over 18 months

Code: Select all

Aging3: IIf(DateDiff("m",[Worker Start Date],Date())+(Day([Worker Start Date])>Day(Date()))<13,"0-12 Months",IIf(DateDiff("m",[Worker Start Date],Date())+(Day([Worker Start Date])>Day(Date()))<19,"13-18 Months","> 18 Months"))

The ussue is that until they hit 19 months, it doesn't show them as >18 because the DateDiff is returning only whole numbers, not decimals.

How can I get it to recognize that 18 months + 1 day is >18 months?

[HansV]

Isn't it simply

Code: Select all

Aging3: IIf(DateDiff("m",[Worker Start Date],Date())+(Day([Worker Start Date])>Day(Date()))<12,"0-12 Months",IIf(DateDiff("m",[Worker Start Date],Date())+(Day([Worker Start Date])>Day(Date()))<18,"13-18 Months","> 18 Months"))

[Abraxus]

Guess I never tested my formula well enough...your simple change seems to have done it!

Thanks, Hans!!