Calculate next service month based on dates

[BoyWonder]

Hi,

I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.

I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.

For example:

Start date: 01/01/2014
End date: 31/12/2015

service frequency: Every 4 months

Using the above information the service months are:
1 - April 2014
2 - August 2014
3 - December 2014
4 - April 2015
5 - August 2015
6 - December 2015


based on the above information and todays date the next service should be in April 2015.

how can I get access to give me this information?

any help will be greatly appreciated ... I'm actually losing sleep over this!

thank you.

 

[Minty]

Lookup the DateAdd function http://www.techonthenet.com/access/functions/date/dateadd.php

 

[namliam]

Depends on your question in detail, but the dateadd function is a good place to start

Do you want the whole list ?
Do you want the next service interval based on todays date?
Query or VBA solution ?

 

[BoyWonder]

I've looked at the DateAdd function but I cant figure out how it can give me the next service month based on the todays date.

namliam, I want the next service interval based on todays date as a VBA solution, how would I go about achieving this result?

thank you fro your suggestions, I've never used a forum for access help and am glad that there are people like you around!

 

[Minty]

It would help if you gave us your gave us an example of your data layout and or a example date set.
However assuming you have a form with the controls on it called TestInt and NxtSvcDue try something like;

Me.NxtSvcDue = DateAdd("m",Me.TestInt,Date())

 

[namliam]

Minty is close but I would refine it a little
First find out the difference in months between todays date and the startdate, using the datediff function (RealMonths)

Next find out how many intervals that is by deviding RealMonths by the interval period.
Here you need to decide on like today you would get 12 months, do you want to see Januari 2015 as the interval or April 2015? (IntevalNumber)

Finaly use the dateadd function (again) to renumerate the 1/1/2014 into the interval by multiplying IntervalNumber * Interval

Lookup both functions and try them out a bit, see how far you get.
Come back here if you get stuck

 

[BoyWonder]

Thanks guys, I will have a play with what you've suggested and come back to let you know how I get on. Your help is greatly appreciated!

Have a good weekend.