Using Access 2010.

I'm trying to calculate the number of months difference between a date in a field and today's date. I used VBA something like this on "On Change"

Private Sub BeginningPeriodDT_Change()
Me.CurPeriod = DateDiff("m", Date, Me.CurPeriod)
End Sub


Of course, this isn't working which why I'm asking on here. Search really didn't get me anywhere so I figure I would ask.

What is not working? Can you give some examples with the returned results?

The basic synthax is correct. Below is an example that works correctly.



I am guessing it may have to do with your date format. Perhaps you should use the SQLDate function found on Allen Browne's site http://allenbrowne.com/ser-36.html. Something like:

...months difference between a date in a field and today's date...

between the date in WHICH field and today's date? On the face of it, your datediff expression doesn't make sense.

If you enter in an unbound Text box the following, it should work:

=DateDiff("m",[CurPeriod],Date())

Edit: Sorry everyone!!!

RAZMaddaz

OK! I got it working. I took out Me. in the formula and it worked. Thanks for the help guys!

--- Edit .... Sorry .... misread the question, so this reply while somewhat relavant, does not really fit! ----


The issue that may be occurring is that DateDiff() will only work in the interval you specify, so ...

DateDiff("m", #1/31/2012 11:59:59 PM#, #2/1/2012 12:00 AM#)

will return a value of 1 month, even though the two dates differ by 1 second. This makes since if you truncate both dates to a granularity of a month. By doing that you can see that DateDiff() is giving the difference between #1/2012# and #2/2012#, which is indeed one.

To prevent this effect and to have your expression return the whole number of months elasped, you have to do something like this:

DateDiff("m", yourDate, Date()) + (DateAdd("m",DateDiff("m", yourDate, Date()),yourDate) > Date())


What the expression does is verify that the value of months returned from the DateDiff(), once added to your base date EXCEEDS the base date. So ...

So ....
DateDiff("m", yourDate, Date()) + (DateAdd("m",DateDiff("m", yourDate, Date()),yourDate) > Date())

DateDiff("m", #1/31/2012#, #2/1/2012#) = 1
DateAdd("m",DateDiff("m", #1/31/2012#, #2/1/2012#),#1/31/2012#) = #2/29/2012#

Now substituting, and keeping in mind that a logical expression has a value of -1 for True and 0 for False ...

1 + (#2/29/2012# > #2/1/2012#)
1 + (-1) = 0

So 0 elapsed months has passed between #1/13/2012# and #2/1/2012#

But now use 1/1/2012 for yourDate and 2/1/2012 for Date() and the result will be 1.

Interesting that you say its working without the Me ... it would seem your code should be ... (note the change to the AfterUpdate event since the Change event will cause the code to fire with each keystroke, which is not something you want)



But you could avoid code and just use ...

=DateDiff("m", Date, [BeginningPeriodDT])

In the Control Source expression of the text box control of CurPeriod. By the way, are you storing CurPeriod in a table? --- if so, you really should not be.