How to round down dates in Access

[jbone]

Hello Access Community,

My name is Gerard and I am new to this forum. It is nice to meet you all!

My first question I have is rounding down dates in Access. I seem to be having trouble with the equation I built below:

Months Remaining: IIf(Date()<[Anniversary],Round(DateDiff("m",Date(),[Anniversary],Round(DateDiff("m",Date(),[Anniversary]+12)))))

My goal with this formula is to produce a number of months remaining (rounding down) by subtracting today's date from the anniversary date. The Round function I put in there seems to not work as the result is the same when I remove it.

Another thing I am also trying to accomplish is having the date pushed forward if todays date is greater than the anniversary date. In this instance, I tried adding 12 months to get it back on track. So say the anniversary date is july 4th 2015 and todays date is aug 4th 2015, well thats gonna show negative 1 but if I add 12 it should bring it to 11 months remaining -which would make sense because the anniversary month and day is fixed but the years just get pushed.

I hope im not bringing too much confusion here with this.



thanks in advance!

[June7]

DateDiff already rounds to whole number. For instance:

Datediff("m", #1/1/2015#, #8/3/2015#) = 7

Datediff("m", #1/1/2015#, #8/20/2015#) = 7

[jbone]

hey June7,

thanks for your prompt reply.

Is there a way to round down when it passes the day?

say for instance, the anniversary date is 13-Mar-16 and we are using today's date (Aug 4th 2015). I would want the formula to tell me that there are 7 months left as it should but if today's date happened to be passed the 13th day of the month then I would want it to round down to 6 months remaining.

hope that makes sense.

thanks!

[June7]

Consider:

DateDiff("m", Date(), [Anniversary]) - IIf(Date() > [Anniversary], 1, 0)

or

DateDiff("m", Date(), [Anniversary]) - IIf(Day(Date())>Day([Anniversary]), 1, 0)

[jbone]

Thank you very much June7! really appreciate the help.

is there any way I can push the Anniversary date forward a year if the Anniversary date is less than today's date?
say for instance, the Anniversary date is 09-May-15 today's date is Aug 5-15, well that would result in -3 instead of 9. The Anniversary day and month remain the same, I just need it pushed by years if its less than today's date.
hope that makes sense.

thank you again for the help

[June7]

I don't understand your data. Is the Anniversary field changed each year to update with the new year? Anniversary for what? What is this data for?

Maybe something that compares the month parts.

IIf(Month(Date())>Month([Anniverserary]) ...

You might want to learn about all the functions for manipulating dates:

http://www.techonthenet.com/access/functions/index.php

https://support.microsoft.com/en-us/kb/210604

[jbone]

hey June7,

I actually got it figured out. I created a update query for the Anniversary date.

Iff([Anniversary]<Date(), DateAdd ("yyyy", 1, [Anniversary]), [Anniversary])

Thank you again for help!