how to calculate service period in years, months and days

[Jon]

Greeting,

I have a form that has two textbox which they service start date and service
end date. Also, it has a three text boxes to represent the service period.
One of them is for Year, the second is for months and the last one is for
days. What I need is to calculate the service period based on the start and
end by using the above three text boxes.

Thanks

 

[Jeff Boyce]

Jon

It sounds like you are saying that you'd first need to know the difference
between the dates in "years" ... take a look at the DateDiff() function for
a way to do this.

If you wanted to know (remaining) months, one way to do this would be to
take the DateDiff() in months, then use the Mod() function to get a
remainder after dividing by 12.

You could use a similar approach to calculate "days" (but I'm not so sure
this would be exact, given leaps years and all).

All of this is untested speculation ... actual mileage may vary...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

Thank you Jeff, but this can be done in Excel!!! What about Access? ):

 

[Jeff Boyce]

Jon

My answer was how to do it in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jon]

Jeff,

I tried it, but the result is #NAME !! please advice??

 

[James A. Fortune]

Greeting,

I have a form that has two textbox which they service start date and service
end date. Also, it has a three text boxes to represent the service period.
One of them is for Year, the second is for months and the last one is for
days. What I need is to calculate the service period based on the start and
end by using the above three text boxes.

Thanks

See:

http://groups.google.com/group/microsoft.public.access/msg/d7c1e78c092cdd13

Please let me know if you discover any problems at all with those
expressions or if you need help seeing how to make the recommended
changes for adapting the expressions to use two dates.

James A. Fortune
(e-mail address removed)

 

[Jeff Boyce]

Ah, that's new information.

When Access gives you #NAME, it's telling you that it couldn't find an
object spelled the way you spelled it.

Spelling counts. Re-check the name(s) of the objects you are trying to use.

Regards

Jeff Boyce
Microsoft Office/Access MVP