Function issue to calculate age from birthday

[birchw]

Hi,

I downloaded a function from the Microsoft Knowledge base to calculate a persons age as at the current date based on DOB. The function is as follows:

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 Date < DateSerial(Year(Now), Month(varBirthDate), Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

I've tested it in the VB immediate window (Access 2K) by entering
?Age(#17/12/1975#)

This returns 30 - correct, as at today's date - 02/11/2006. However if I enter
?Age(#10/12/1975#), it returns 31. The only reason for this that I can think of is that the date format is being transposed into MM/DD/YYYY format. I've been trying to correct this by formatting the dates using the format function, converting all of the dates to YYYY-MM-DD format, but it still isn't working. I would really appreciate some help on this! Thanks.

[VALIS]

Hi,

I downloaded a function from the Microsoft Knowledge base to calculate a persons age as at the current date based on DOB. The function is as follows:

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 Date < DateSerial(Year(Now), Month(varBirthDate), Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

I've tested it in the VB immediate window (Access 2K) by entering
?Age(#17/12/1975#)

This returns 30 - correct, as at today's date - 02/11/2006. However if I enter
?Age(#10/12/1975#), it returns 31. The only reason for this that I can think of is that the date format is being transposed into MM/DD/YYYY format. I've been trying to correct this by formatting the dates using the format function, converting all of the dates to YYYY-MM-DD format, but it still isn't working. I would really appreciate some help on this! Thanks.

Hi,

This might not be very orthodox declaring varBirthDate as String would avoid the ambiguity with the date format.

[NeoPa]

With date literals for testing - always use form #10 Jan 2006# rather than any form that could be ambiguous.
This is particularly sensible when dealing with databases (SQL) as it won't use the default as set up for your PC in certain circumstances.

[NeoPa]

Hi,

This might not be very orthodox declaring varBirthDate as String would avoid the ambiguity with the date format.

Unorthodox can be good, I wouldn't recommend wasting the benefits of typing of variables here though. Better to understand the problem and get the code right.

[VALIS]

Unorthodox can be good, I wouldn't recommend wasting the benefits of typing of variables here though. Better to understand the problem and get the code right.

Agreed. I was dealing with a problem that didn't exist. The problem was the test not the code. Doh!

[birchw]

Thanks guys for your prompt responses. I've incorporated the function into my database, rather than testing it in the immediate window and the results are OK. You learn something new every day...

[PEB]

To assign a format that you want you can use the following:

Cvdate(Format("04/03/2006","dd/mm/yyyy")) and it should be considered as 04 mars 2006

:)