Access 2003 - Find the age using datediff

I have Access 2003 - What I am trying to do is pull out anyone who is at a certain age when a proceudre was done. For example: jane Doe was 17 on this date. I have the birthdate and the date of service.
I am using this is the query AgeAtProcedure: (DateDiff("yyyy",[PatientBirthDate],[FromDate]))
It is returnign the age as example - born 10-22-89 and date of service of 10-1-1989 as 18 - I want it to be 17 because at 10-1-1989 they are still 17. I don't want it to show 18 until 10-22-2007.
PdetersAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Hello Pdeters,

Have a look at the YearsMonthsDays function described here:
http://vbaexpress.com/kb/getarticle.php?kb_id=866

You could use it like this:

SELECT Val(YearsMonthsDays([PatientBirthDate], [FromDate], True)) AS PatientAge
FROM SomeTable

Regards,

Patrick
0
 
mbizupCommented:
This should give you the exact age:

cdbl(DateDiff("d",[PatientBirthDate],[FromDate]))/365
0
 
PdetersAuthor Commented:
what about leap year - If the date of service is close to hteir birthdate of turning 18 (minor) it doesn't catch that one
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Paste the function below into a module.  Then call from your query.  I assumed your dates where strings.

AgeAtProcedure:Age([PatientBirthDate],[FromDate])


JimD

Function age(varBirth As Variant, varService As Variant)

  Dim varAge As Variant
 
  varAge = DateDiff("yyyy", DateValue(varBirth), DateValue(varService))
  If Month(DateValue(varBirth)) < Month(DateValue(varService)) Then
    varAge = varAge - 1
   ElseIf Month(DateValue(varBirth)) = Month(DateValue(varService)) And Day(DateValue(varBirth)) < Day(DateValue(varService)) Then
     varAge = varAge - 1
  End If
   
  age = CInt(varAge)

End Function
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I assumed your dates where strings.>>

  Note if they are actual date/time fields, you can pull out all the DateValue()'s in the function.

JimD
0
 
PdetersAuthor Commented:
yes they are actual date/time fields.
When you say "call" from my query - would i be calling age ?
0
 
PdetersAuthor Commented:
Sorry again - would I also just take out all of htese then in the module.
DateValue(varBirth), DateValue(varService)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<When you say "call" from my query - would i be calling age ?>>

  yes, you'd be calling the function Age()

AgeAtProcedure:Age([PatientBirthDate],[FromDate])

<<Sorry again - would I also just take out all of htese then in the module.
DateValue(varBirth), DateValue(varService)>>

  Would look like this then:

Function age(varBirth As Variant, varService As Variant)

  Dim varAge As Variant
 
  varAge = DateDiff("yyyy", varBirth, varService)
  If Month(varBirth) < Month(varService) Then
    varAge = varAge - 1
   ElseIf Month(varBirth) = Month(varService) And Day(varBirth) < Day(varService) Then
     varAge = varAge - 1
  End If
   
  age = CInt(varAge)

End Function
0
 
PdetersAuthor Commented:
I have created the module with the function. When i put it in the query i get this

Undefined function 'age' in expression.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Make sure the module name is not called 'Age".

JimD

0
 
PdetersAuthor Commented:
I did have it called age -

The totals are still not coming up correctly.
1-5-1989 to 6-25-2007 is showing 17
11-10-1989 o 4-26-2007 - is showing 18
should be the other way around.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<should be the other way around.>>

  Sorry.

JimD

Function age(varBirth As Variant, varService As Variant)

  Dim varAge As Variant
 
  varAge = DateDiff("yyyy", varBirth, varService)
  If Month(varBirth) > Month(varService) Then
    varAge = varAge - 1
   ElseIf Month(varBirth) = Month(varService) And Day(varBirth) > Day(varService) Then
     varAge = varAge - 1
  End If
   
  age = CInt(varAge)

End Function
0
 
PdetersAuthor Commented:
Thank you so  much for taking the time. I learned a lot here and I think it stuck.

thanks again
0
 
Patrick MatthewsCommented:
Pdeters,

Please download this file for an example using my suggestion:

Direct link to your file
<https://filedb.experts-exchange.com/incoming/ee-stuff/4937-Example.zip


Regards,

Patrick
0
 
PdetersAuthor Commented:
THanks -
0
 
Patrick MatthewsCommented:
Pdeters,

Did you even try to implement my suggestion before you accepted Jim's?

Regards,

Patrick
0
 
PdetersAuthor Commented:
Sorry I did not - I will try it right now -
0
 
PdetersAuthor Commented:
I get a data type mismatch when I try and pull out a certain age.
any ideas what I need t change
0
 
PdetersAuthor Commented:
It does give me the correct answer.
0
 
PdetersAuthor Commented:
I copied downloaded your example file - and that is owrking fine when you choose criteria - I will work with mine more. And change how i credited the points. Sorry I did ot look at your solution.
0
 
PdetersAuthor Commented:
thanks again it is working great
0
 
Patrick MatthewsCommented:
Pdeters,

Thanks for that.  When the Q does get reopened, I have zero objection to a split with Jim,
BTW...

Regards,

Patrick
0
 
PdetersAuthor Commented:
Thanks again - and am really sorry

0
 
PdetersAuthor Commented:
THanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.