DateDiff problem

  • Thread starter Thread starter p cooper
  • Start date Start date
P

p cooper

I want to display a load of ages on 1-APR- 04 ( in UK)
A query with 2 calculatd fields

Age(YRS): (DateDiff("m",[ChildsDoB],#01/04/2004#))/12
Age(MNTHS): (DateDiff("m",[ChildsDoB],#01/04/2004#)) MOD 12
eg
if the DoB is 4/4/86 this comes out as 18 yrs 0 months
24/2/88 16 yrs 2 months

they shouldnt - in the first the 18th birthday is in 3 days time and
the age should be 17 yrs 11 months
in the secind it should be 16 yrs 1 month (2 months on 24th of the
month)

1. Whos problem is this
2. Any way around it ?
 
Both the year and month arguments for DateDiff will return a number based on
subtracting the current year value from the starting year value, or the
current month value from the starting month value. In other words, the
DateDiff will return a value of 4 months if you start with 8/15/2003 and use
12/1/2003 as the final date. Same type of thing for year: start with
12/31/2003 and end with 1/1/2004 and you'll get 1 year.

You'll need to use some modified code. One of the other MVPs posted some
code that will do what you seek within the last month or few weeks, but I
don't have a copy of it. And my quick search on Google didn't find it.

Essentially, the code that was posted for the "correct" months involved
comparing the day portion of the start and end dates, and if the start day
is greater than the end day, subtract 1 from the month value. And for the
year value, you need to compare the month and day values.

See if you are more successful than I on Google looking for this. Post back
if you need more help and I'll see if I can find/reproduce the code.
 
Try the "More Complete DateDiff Function" Graham Seach and Doug Steele wrote.
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

You specify how you want the difference between two date/times to be calculated
by providing which of ymdhns (for years, months, days, hours, minutes and
seconds) you want calculated.


Ken said:
Both the year and month arguments for DateDiff will return a number based on
subtracting the current year value from the starting year value, or the
current month value from the starting month value. In other words, the
DateDiff will return a value of 4 months if you start with 8/15/2003 and use
12/1/2003 as the final date. Same type of thing for year: start with
12/31/2003 and end with 1/1/2004 and you'll get 1 year.

You'll need to use some modified code. One of the other MVPs posted some
code that will do what you seek within the last month or few weeks, but I
don't have a copy of it. And my quick search on Google didn't find it.

Essentially, the code that was posted for the "correct" months involved
comparing the day portion of the start and end dates, and if the start day
is greater than the end day, subtract 1 from the month value. And for the
year value, you need to compare the month and day values.

See if you are more successful than I on Google looking for this. Post back
if you need more help and I'll see if I can find/reproduce the code.

p cooper said:
I want to display a load of ages on 1-APR- 04 ( in UK)
A query with 2 calculatd fields

Age(YRS): (DateDiff("m",[ChildsDoB],#01/04/2004#))/12
Age(MNTHS): (DateDiff("m",[ChildsDoB],#01/04/2004#)) MOD 12
eg
if the DoB is 4/4/86 this comes out as 18 yrs 0 months
24/2/88 16 yrs 2 months

they shouldnt - in the first the 18th birthday is in 3 days time and
the age should be 17 yrs 11 months
in the secind it should be 16 yrs 1 month (2 months on 24th of the
month)

1. Whos problem is this
2. Any way around it ?
Click to expand...
Click to expand...
 
Back
Top