DateDiff

[mrkaye]

I have an expression for my query, DateDiff("yyyy",[DOB],now())


This works to a point, if DOB has not happened this year yet, than the years are off by one year. is there a way to correct this problem?
TIA to all

[Rixxe]

There are plenty (And i mean plenty) of sites on the internet that have this question asked loads of times... (Because i searched for it myself).

I won't link you sites, as i'm sure you are able to google it... However i will link this:
Note i've been using it in SQL Server, however it should give you an idea about how to solve the problem...

ALTERFUNCTION [dbo].[GetAge](@DOB datetime, @Today Datetime)RETURNSInt
AS
Begin
Declare @Age AsInt
Set @Age =Year(@Today)-Year(@DOB)
IfMonth(@Today)<Month(@DOB)
Set @Age = @Age -1
IfMonth(@Today)=Month(@DOB)andDay(@Today)<Day(@DOB)
Set @Age = @Age - 1
Return @Age
End

Good luck.

[mrkaye]

Thanks Rixxe, I will give it a try
Marty

[Rixxe]

This might be useful for you:

http://www.everythingaccess.com/tuto...-given-the-DOB

Provided by Allen Browne


Good luck

[mrkaye]

Again want to say thanks, is there a code for year, month, and day? so I could extend the function to include the years and months, and days?

[Rixxe]

So you want to know the days and months, between DOB and Today() for example?

I'm sure if you have a go, you might be able to add it in to the query.

This might give you an idea:

DateDiff("yyyy",tbl.DOB,tbl.Date)+Int(Format(tbl.D ate,"mmdd")<Format(tbl.DOB,"mmdd")) AS Age

[mrkaye]

Thanks Rixxe, I will try to work it in some how?
Just to say THANKS again, just taking the time to help me.
Marty

[websterh]

I have found this code to be useful too.

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

I've used this and tested its accuracy and it updated the age when i entered a date -1 day and == to the DOB

[mrkaye]

It works as you said. the year...
I have tried the year, month and day code and I can't get it to work, I'm a novice at this, it looks like I will need a complete code and expression that already works. Can you help? or can you send me to a URL that has it, I've tried with no luck.

Again thanks Rixxe

[Rixxe]

Well you are in luck (Hopefully!)

This site (Thanks to RG) gives a great example:
http://www.mvps.org/access/datetime/date0001.htm

You want to find the age of someone at a certain date, Is the certain date always TODAY() ?
I'll see what i can find out to help you... hopefully someone else might have come across the problem before.

EDIT: Site for calculating age in years and months:
http://www.tek-tips.com/faqs.cfm?fid=5876

[mrkaye]

As I said the year expression you sent works fine just as you thought.
Now I'm trying to complete what I really want and hope you can or someone can help.

A function that is complete, with the expression that goes with it. Year, Month, Day. I can modify it with my [DOB] inplace of ?. I'm really a total novice at this but i'm trying.

All HELP Welcome
mrkaye

[Rixxe]

Ok, well the [DOB] is the a date of birth field.
So you can change '[DOB]' to '2010/01/01' if you only want to see 1 result for all records (If they all have the same DOB, or you can change it to another field.)

You can also change the 'Now()' part of the code, Now() or Today() represents todays date, so you could shove a field in there if you wish, or again '2010/01/01' etc etc

Now as far as i know, when using DateDiff() You can only specify the following:
Year
YY
DD
MM
YYYY
etc etc
Never tired just selecting DateDiff(yyymmdd, ......,.....)

[mrkaye]

Rixxe I can change the DOB field to what ever, and I dont need a datediff function. any function that will give me "like 35 years 7 months 5 days" based on the date of birth, I would also need the expression that goed with the function. if its possible.
TIA
Marty

[Rixxe]

After searching for a long time, i found this:
You will have to alter it, as it's supposed to be displayed differently from how you want it.
Alas i don't work with VBA enough to help you with that, so hope this is useful for you...

Function fAgeYMD(startdate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days
 
Dim intHold As Integer
Dim dayHold As Integer
 
   intHold = Int(DateDiff("m", startdate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), day(startdate)))
 
   If day(EndDate) < day(startdate) Then
      dayHold = DateDiff("d", startdate, DateSerial(year(startdate), month(startdate) + 1, 0)) + day(EndDate)
   Else
      dayHold = day(EndDate) - day(startdate)
   End If
 
   fAgeYMD = Int(intHold / 12) & " year" & IIf(Int(intHold / 12) <> 1, "s ", " ") _
             & intHold Mod 12 & " month" & IIf(intHold Mod 12 <> 1, "s ", " ") _
             & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")
 
End Function

Good luck



(Note: This is not my code)

[mrkaye]

Rixxe I will try to modify this function, it looks like it would do the job?

I really appreciate what you must have gone thru.

I will let you know what happens.
TIA
Marty