Calculating age in years and months

[RMCDD997]

How can I format DateDiff to display the age in years and months? I use a dob
(date of birth) and a Today field. DateDiff would be used in a query.

[fredg]

On Wed, 1 Aug 2007 16:16:00 -0700, RMCDD997 wrote:

> How can I format DateDiff to display the age in years and months? I use a dob
> (date of birth) and a Today field. DateDiff would be used in a query.

You can't using just DateDiff.
You can if you use this function:

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[James A. Fortune]

RMCDD997 wrote:
> How can I format DateDiff to display the age in years and months? I use a dob
> (date of birth) and a Today field. DateDiff would be used in a query.

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

Post back if you need help implementing this.

James A. Fortune
MPAP...@FortuneJames.com

[RMCDD997]

Thanks for you reply fredg. In a form, I have a "DOB", "Today" & an "Age"
text box. I copied & debugged the Date2Diffs module. "Age" is unbound using
the control source of: "=Diff2Dates("ymd",[dob],[today],True)" (minus
quotes). When I change to form view, I receive the error #Name? in the "Age"
text box. Any ideas? Thanks

[RMCDD997]

Thanks for you reply James. Could you read my replay to "fredg"? Thanks.

[Fr Stephen Moore]

I have a 40-second delay in printing from Vista; as well, the screen freezes
until the Canon laser printer starts. This is despite the firewall between
Vista and the printer being off. Canon support can't help.

Anyone out there with a remedy to allow me to keep writing while the printer
is preparing to print?

Thanks.

"RMCDD997" <RMCD...@discussions.microsoft.com> wrote in message
news:BC72E3E5-AD85-4F50...@microsoft.com...

[fredg]

Well, if you are going to use Access, you should use Access defined
functions. Today is an Excel function.
In Access Date() will return the current date.
Now() will return the current date and time.

You have enclosed Today within brackets, so Access is looking for a
field named Today. That will cause the #Name error.

Try:
=Diff2Dates("ymd",[dob],Date(),True)

[John W. Vinson]

On Sat, 4 Aug 2007 11:24:17 +1000, "Fr Stephen Moore"
<gresfor...@bigpond.com> wrote:

>I have a 40-second delay in printing from Vista; as well, the screen freezes
>until the Canon laser printer starts. This is despite the firewall between
>Vista and the printer being off. Canon support can't help.
>
>Anyone out there with a remedy to allow me to keep writing while the printer
>is preparing to print?
>
>Thanks.

Stephen, you're asking in the wrong place (twice) - once by asking in a
newsgroup supporting the database software Microsoft Access, and second by
asking in an ongoing discussion of a totally unrelated subject!

Please repost your question in a Vista newsgroup. Watch out for trolls,
there's a bad infestation over there unfortunately. There have been a LOT of
problems reported with Vista printer drivers, though.

John W. Vinson [MVP]

[RMCDD997]

I appreciate your response. "Today" is the name of the text box, the control
source is "Date()". I failed to mention I was using access 2007.

[James A. Fortune]

RMCDD997 wrote:
> Thanks for you reply James. Could you read my replay to "fredg"? Thanks.

I created a table called tblAny and a form called MyForm with a textbox
called Today with a default value of =Date() for the purpose of running
a test:

tblAny
AID AutoNumber
DOB Date/Time
AID DOB
1 4/9/2006
2 2/2/2003
3 7/3/2004
4 7/18/2005

Forms!MyForm!Today.Value = #8/4/2007#

qryElapsedTimeSinceBirthday:
SELECT DOB, Int(Format(Forms!MyForm!Today.Value, 'yyyy.mmdd') -
Format([DOB], 'yyyy.mmdd')) & ' year(s)/' & (12 +
Int(Format(Forms!MyForm!Today.Value, 'mm.dd') - Format([DOB], 'mm.dd')))
Mod 12 & ' month(s)/' & Day(Forms!MyForm!Today.Value) - Day([DOB]) +
Abs(Day([DOB]) > Day(Forms!MyForm!Today.Value)) *
Day(DateSerial(Year([DOB]), Month([DOB]) + 1, 0)) & ' day(s)' As
ElapsedTime FROM tblAny;

!qryElapsedTimeSinceBirthday:
DOB ElapsedTime
4/9/2006 1 year(s)/3 month(s)/25 day(s)
2/2/2003 4 year(s)/6 month(s)/2 day(s)
7/3/2004 3 year(s)/1 month(s)/1 day(s)
7/18/2005 2 year(s)/0 month(s)/17 day(s)

Now that the test seems to work, change the expression to get the [DOB]
value from the form:

Age.ControlSource
= Int(Format(Today.Value, 'yyyy.mmdd') - Format(DOB.Value, 'yyyy.mmdd'))
& ' year(s)/' & (12 + Int(Format(Today.Value, 'mm.dd') -
Format(DOB.Value, 'mm.dd'))) Mod 12 & ' month(s)/' & Day(Today.Value) -
Day(DOB.Value) + Abs(Day(DOB.Value) > Day(Today.Value)) *
Day(DateSerial(Year(DOB.Value), Month(DOB.Value) + 1, 0)) & ' day(s)'

James A. Fortune
MPAP...@FortuneJames.com

[thy...@gmail.com]

Hi all,

Thanks for the help. I got the solution that I want.