RoundUP in a control with DateDiff (1 Viewer)

[WineSnob]

I have a control I need to roundup to the nearest year. Here is the control source i am using =DateDiff("y",[txtIssueDate],[txtIncomeStartDate])/365.242199
It returns 4.89538176283 using 5/9/2009 as [txtIssueDate] and 4/1/2014 as [txtIncomeStartDate]
How can I get it to return 5 ?

 

[CraigDolphin]

Well, there is a simple rounding function (http://www.techonthenet.com/access/functions/numeric/round.php) available in access. However, this rounds up or down to the nearest number of specified decimal places.

Thus

=Round(DateDiff("y",[txtIssueDate],[txtIncomeStartDate])/365.242199,0)

...would return 5 given the inputs you mentioned.

However, if your datediff returned a value of, say, 4.2 then it would return a value of 4.

If you really want it to always round UP to the next highest number, then you could just use something like:

=Int(DateDiff("y",[txtIssueDate],[txtIncomeStartDate])/365.242199) + 1

 

[WineSnob]

Perfect. Thanks