Count months not days

[Scott]

Hi All,
Just wondering if anyone can help me with a small issue I have.

I need to figure out how many months (to the nearest month) a client has
been 'with me' in a query.

I have the 'Start Date' that they came on board, and obviously todays date.

If I do something like Date() - mas_StartDate, I get the number of DAYS that
they have been a client, but they are charged in whole months, so I need to
either format, or calculate the above based on months not days.

If anyone can help me with this one it would be much appreciated

Thanks in advance!
Scott.

 

[Marshall Barton]

I need to figure out how many months (to the nearest month) a client has
been 'with me' in a query.

I have the 'Start Date' that they came on board, and obviously todays date.

If I do something like Date() - mas_StartDate, I get the number of DAYS that
they have been a client, but they are charged in whole months, so I need to
either format, or calculate the above based on months not days.

Generally, you should use the date related functions to do
calculations on date/time values. In this case, I think
this might be sufficient:
DateDiff("m", mas_StartDate, Date())

 

[John W. Vinson]

Hi All,
Just wondering if anyone can help me with a small issue I have.

I need to figure out how many months (to the nearest month) a client has
been 'with me' in a query.

I have the 'Start Date' that they came on board, and obviously todays date.

If I do something like Date() - mas_StartDate, I get the number of DAYS that
they have been a client, but they are charged in whole months, so I need to
either format, or calculate the above based on months not days.

If anyone can help me with this one it would be much appreciated

Thanks in advance!
Scott.

DateDiff("m", [mas_StartDate], Date())

will work... just be aware that it calculates month boundaries, not "thirty
days or so" full months; so if mas_StartDate were August 31 and you ran the
query today, it would give 1 month.

John W. Vinson [MVP]

 

[Scott]

Thanks guys- that is exactly what I was after!

Hi All,
Just wondering if anyone can help me with a small issue I have.

I need to figure out how many months (to the nearest month) a client has
been 'with me' in a query.

I have the 'Start Date' that they came on board, and obviously todays
date.

If I do something like Date() - mas_StartDate, I get the number of DAYS
that
they have been a client, but they are charged in whole months, so I need
to
either format, or calculate the above based on months not days.

If anyone can help me with this one it would be much appreciated

Thanks in advance!
Scott.

DateDiff("m", [mas_StartDate], Date())

will work... just be aware that it calculates month boundaries, not
"thirty
days or so" full months; so if mas_StartDate were August 31 and you ran
the
query today, it would give 1 month.

John W. Vinson [MVP]