Get Age from BirthDate field in a Query 1

[CinS]

Can anyone tell me how to create a new field that gives the Age, based on an existing [BirthDate] field in a query. I don't know if it should be a function or expression, or if it should be VB or SQL and I don't understand how to write it. Then, how do you add it to a new column in a query?

Your assistance is greatly appreciated!

 

[scriverb]

You can create another column in your query and paste the following code in the Field row of the new column:

AGE: CInt((DateDiff('d',tblYourTable.Birth_Date,Date())/365.25)-0.5)

You will have to replace the table name with your table name.

Bob Scriver

 

[CinS]

Thank you for the code. Evidently I have the data type messed up for the [BirthDate] field. I originally had it as text because I imported it from a .txt file and it wouldn't convert dates. Your code didn't work with it, came up with "#Error" in the Age field when I run it . I changed the data type of the original table to number, Long Integer and it still comes up with #Error. It won't let me convert it to "date/time" data type.

Any ideas?

Thanks,
CinS

 

[MichaelRed]

Three:

The (simplistic) DateDiff function cited above will, in general be off by 1 in half of the times it is used.

Do a search on basDOB2Age in these forums for the correct soloution.

Try converting the text fields to date type in a NEW field.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[FMezler]

Whenever I have problems with Data types that I import from a text file, I just make a New field in my table with the correct format I want. Then I create an Update Query that updates the contents of the New Field with the data in the field I pulled from the text file. Once the Update is complete, I delete the field that held the data in the INCORRECT format. Might be an easier way, but it hasnt failed me yet.

 

[scriverb]

MichaelRed: I am fairly new to Tek-Tips although I am not fairly new to programming(34 years) Before you start bad mouthing code from someone trying to provide assistance to someone here in this forum, maybe you should truly take a look at the suggestion that was made and learn something. I usually don't say anything in these forums when there are opposing viewpoints; but when you start to make derogatory and arrogant remarks that are patently wrong, I believe clarification is warranted so less experienced individuals are not inappropriately influenced by your bias.. My suggestion is that you test the expression against a datafile of birthdates and see how many are wrong.

AGE: CInt( (DateDiff('d',tblYourTable.Birth_Date,Date())/365.25) -0.5)


The DateDiff code while simplistic, does work well and is extremely accurate. It is more than a simplistic DateDiff calculation. I am well aware of the downfall of just doing a datediff on “d”(days) calculation between a birthdate and todays date and then dividing by 325.25. Surely, this will be off half of the time when the resulting real number has a remainder that is .50 or greater. Yes, someone that calculates at 54.5160848733744 will come out as 55 years old if that is all that this "simplistic code" did to the calculation. But, it does more. I am aware that the rounding up factor will create a 50% error rate if we left it at that. You should LOOK at the code suggested and see that before I performed the CInt function, which is where the rounding takes place, I subtracted .5 years from the initial calculation. This would put the above real number at 54.0160848733744. The real number does not round up but has the decimal portion truncated off through the rounding down with a result of 54 - which is the SIMPLE and CORRECT age calculation for a birthdate of 11/21/1947. This works for all dates where the decimal portion calculates at .5 or greater. For those that calculate less than .5 (54.4996577686516) the same subtraction of .5 years takes place which lowers the real number below the correct age to 53.9996577686516 which when rounded up during the CInt function gives us the CORRECT age of 54 once again.(DOB 11/27/1947).. Now the only issue that should be brought to bear here is the Leap Century adjustment. If it were necessary to perform celestial calculation of the expansion of the universe then it would make sense to put this adjustment into play. But, since we are only calculating how old someone is I felt it was uncessary.

Last line of your post which I thought was quite telling:
“There is never time to do it right but there is always time to do it over”

Where here is mine:
“There is never anything wrong with a simplistic approach to a problem if the results are correct.”

CinS: My apologies for this posting in your question thread. I felt that it was necessary to speak up. There are many ways to calculate the Age of someone. There have been other posts that have provided functions and expressions that all perform the same basic calculation. This is just one of the methods that I use and it has served me well. Yes, it works and you can use it with confidence.

Bob Scriver

 

[scriverb]

Adjustment for Birthdays: DOB's that happen to be birthdays when compared to todays date need to be handled slightly differently or they will be off by one day and will not rollover to the next age until the next day. The following adjustment handles even those situations.

IIf(DatePart("m",[tblYourTable]![BIRTH_DATE])=DatePart("m",Date()) And DatePart("d",[tblYourTable]![BIRTH_DATE])=DatePart("d",Date()), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)-0.5))

The IIF compares the month and day of the date of birth field to system date month and day. If the result is true it means that this is the birthdate of the individual and the subtraction of the .5 is not necessary. Otherwise, the calculations require the subtraction of .5 before rounding.

Bob Scriver

 

[Guest_imported]

Have you tried this?

Right$(Year(Date()-[birth_date]),2)

This one always seems to work

 

[sebkerr]

Try this. It seesm to work fine, regardless of if it is your birthday or not.

Right$(Year(Date()+1-[birth_date]),2)

Hope this helps

Sebkerr

 

[scriverb]

Right$(Year(Date()+1-[birth_date]),2)

The above equation seems to work for ages up to 99 years but greater than 99 starts over at 00 years.(100 returns 00, 101 returns 01, etc.)

So in most instances it works okay.
Bob Scriver