Date Calculation

[jon_russ]

Hi guys,

I am still trying to make the DB for my sisters dance company.

I have managed to calculate the students ages from the DOB field, but I also need to know their ages at the 31 March and 1 December for competition purposes.

I am using the following code as a module:.....

Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant
If IsNull(varBirthDate) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varBirthDate, Now)
If DOB < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

.....to calculate the age.

The age field contains the following in the ControlSource property of the text box:
=Age([DOB]) & " yrs "

Please could you suggest a method that would allow meto calculate the ages as of 31 March & 1 Dec of the current year.

TIA

Jon

 

[raskew]

Hi-

The problem with your Age() function is that it doesn't provide the option of selecting a date other than today's date.

Here's a query based on Northwind's Employees table, which provides all three ages. You'll just need to substitute your table and field names.

SELECT
    Employees.LastName
  , Employees.FirstName
  , Employees.BirthDate
  , DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([Birthdate]),Day([BirthDate]))) AS ageNow
  , DateDiff("yyyy",[BirthDate],DateSerial(Year(Date()),3,31)+(DateSerial(Year(Date()),3,31)<DateSerial(Year(Date()),Month([Birthdate]),Day([BirthDate])))) AS age331
  , DateDiff("yyyy",[BirthDate],DateSerial(Year(Date()),12,1)+(DateSerial(Year(Date()),12,1)<DateSerial(Year(Date()),Month([Birthdate]),Day([BirthDate])))) AS age121
FROM
   Employees;

HTH - Bob

 

[IanH]

I've done something similar to what (I believe) you are trying to do.

I've output the code and a simple form into the attached database (winzipped). What I wasn't sure about was whether you were wanted to check against 31/12 and 31/03 this year or, for example if we were in June, 31/03 next year, but I'm sure you can amend the logic to fit.

Regards

Ian

 

[MarkK]

Fast and dirty

Subtract one date from another to return the number of days, and then divide by the number of days in a year to return the number of years. Use Abs() and the number is always positive.

Function YearDiff(d1 As Date, d2 As Date) As Byte
YearDiff = Abs((d2 - d1) / 365.25)
End Function

 

[Mile-O]

Function YearDiff(d1 As Date, d2 As Date) As Byte
YearDiff = Abs((d2 - d1) / 365.25)
End Function

Definitely dirty; it's not always accurate.

 

[MarkK]

Definitely dirty; it's not always accurate.

Hey, it only returns a byte. Might be off by a day if you happen to be 255 years old.

 

[IanH]

Hey, it only returns a byte. Might be off by a day if you happen to be 255 years old.

Have used this (and variants on this) in the past.

It works if you don't need to be absolutely accurate - can have problems if the birthday is on the same day as the date you are comparing against.