Solved

# Access 2003 - Find the age using datediff

Posted on 2007-10-05
924 Views
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.
0
Question by:Pdeters

LVL 61

Expert Comment

This should give you the exact age:

cdbl(DateDiff("d",[PatientBirthDate],[FromDate]))/365
0

Author Comment

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

LVL 92

Accepted Solution

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

LVL 56

Expert Comment

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

LVL 56

Expert Comment

<<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

Author Comment

yes they are actual date/time fields.
When you say "call" from my query - would i be calling age ?
0

Author Comment

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

LVL 56

Expert Comment

<<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

Author Comment

I have created the module with the function. When i put it in the query i get this

Undefined function 'age' in expression.
0

LVL 56

Expert Comment

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

JimD

0

Author Comment

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

LVL 56

Assisted Solution

<<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

Author Comment

Thank you so  much for taking the time. I learned a lot here and I think it stuck.

thanks again
0

LVL 92

Expert Comment

Pdeters,

<https://filedb.experts-exchange.com/incoming/ee-stuff/4937-Example.zip>

Regards,

Patrick
0

Author Comment

THanks -
0

LVL 92

Expert Comment

Pdeters,

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

Regards,

Patrick
0

Author Comment

Sorry I did not - I will try it right now -
0

Author Comment

I get a data type mismatch when I try and pull out a certain age.
any ideas what I need t change
0

Author Comment

It does give me the correct answer.
0

Author Comment

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

Author Comment

thanks again it is working great
0

LVL 92

Expert Comment

Pdeters,

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

Regards,

Patrick
0

Author Comment

Thanks again - and am really sorry

0

Author Comment

THanks
0

## Featured Post

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+) Â  Â  as used in Oracle; Â  Â  *= Â  Â  =* Â  Â as used in Sybase â€¦
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if youâ€™re going to set referential integrity: Decide if you want cascade updaâ€¦
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦