Alternate rounding on dates in query?

[Sketchin]

I have a query expression that calculates the number of years between 2 dates using DateDiff. Here is the equation as it is:

BudgetedHQPCalc: Round(DateDiff("yyyy",[DateHired],[DateTerminated]),1)

What I would like this to do, is if the difference is, for example, 1.4, I want the number rounded down, if its 1.6 I want it rounded up, and if its 1.5, i want to use alternate rounding (1.5 down 2, 2.5 up to 3).

Is there an easy way to do this?

 

[plog]

First, your DateDiff() is only going to return an integer so the Round() is moot. Second, your explanation of alternate rounding makes no sense: "1.5 down 2, 2.5 up to 3". Both those examples are normal rounding. Again, its moot because you will never have anything to round from your DateDiff function.

 

[jdraw]

I agree with plog regarding the DateDiff and 'yyyy'. But I'm wondering what would happen with something like
DateDiff("d",[DateHired],[DateTerminated])/365)

Access Round has its own peculiarity
You may want some custom code to do whatever rounding/adjustment you need.

 

[Sketchin]

Thanks Jdraw, doing it your way will work just fine.