getting age from Birthdate 2

[craigey]

Hi

If i have a field for someone's birthday, How can I get a field to automatically fill in the persons age into a field called Age??? I'm fairly new to access so please be thorough and patient with me.

By the way does anyone know of any good guides or things to practice so that I can learn how to use Access.

Many thanks

 

[KenReay]

Hi

See

http://www.mvps.org/access/datetime/date0001.htm

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MichaelRed]

Public Function basDOB2Age(DOB As Date, Optional AsOf As Date = -1) As Integer

    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    'Michael Red 12/15/2001
    'To Calculate Age from Date of Birth
    
    'Sample Useage:
    '? basDOB2Age(#8/21/1942#, #8/21/2022#)
    '80

    '? basDOB2Age(#8/21/1942#, #8/20/2022#)
    '79

    '? basDOB2Age(#8/21/1942#)
    '59


    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim BrthDayCorr As Boolean  'BirthDay Before or After date in question

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    BrthDayCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf   'Check This Year

    basDOB2Age = tmpAge + BrthDayCorr           'Just Years and Correction
    
End Function

MichaelRed
m.red@att.net

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

 

[craigey]

thankyou both for your advice, but i've tried implemeting the code, but am obviously doing something wrong.

I have a field called birthdate (which has been set at medium date format), but I don't know how the Age field should be set up. could you please go through it step- by-step.

thanks!

 

[MichaelRed]

Not at all sure why it should be a problem. Samples within the code show actual results returned from immediate / debug window. Syntax for usage in a query would be as a calculateed field. To assign to a control with the DOB as a field in a form / report, the syntax is again 'different', but standard for the form / control.

If you still need assistance, provide the context in which you are using the procedure, and what error(s) are returned.

MichaelRed
m.red@att.net

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

 

[gnt]

Craigey-
You may have figured this one out already, but if not, and for the future here's how I do it without any VBA-

Put the following in the Control Source property of the Age field:

=DateDiff(&quot;yyyy&quot;,[txtDOB],Now())+Int(Format(Now(),&quot;mmdd&quot<Format([txtDOB],&quot;mmdd&quot)

Where txtDOB is the name of your birthdate field.
-gnt

 

[craigey]

sorry i haven't posted back, but i'm getting the following error.

&quot;the expressin you entered contains invalid syntax. you may have entered an operand without an operator&quot;

the code is in the default value box and is as below.

=DateDiff(&quot;yyyy&quot;[Birthdate],Now())+Int(Format(Now(),&quot;mmdd&quot<Format([Birthdate],&quot;mmdd&quot)


I have two tables, one called admin and one called records. the birthdate and the age are both in the records table. (just thought i'd mention it, in case it was a problem).

Thanks for all your help so far guys. hopefully I will get soon!

 

[craigey]

also how do i make the age field locked, so that only the result is shown and that it is not possible to type over the result when filling in the fields.

 

[KenReay]

Hi

You set the .locked property of the control on the form = true, if you do not want user to be able to click in control, also set .enabled = false

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[craigey]

Thanks, but where do i go to do that? (sorry i'm being really thick)! Do i need to use the queries section?

also can you answer the post as below, as that's my main problem! I realy apreciate all the help!

craigey (TechnicalUser) Oct 11, 2002
sorry i haven't posted back, but i'm getting the following error.

&quot;the expressin you entered contains invalid syntax. you may have entered an operand without an operator&quot;

the code is in the default value box and is as below.

=DateDiff(&quot;yyyy&quot;[Birthdate],Now())+Int(Format(Now(),&quot;mmdd&quot<Format([Birthdate],&quot;mmdd&quot)


I have two tables, one called admin and one called records. the birthdate and the age are both in the records table. (just thought i'd mention it, in case it was a problem).

Thanks for all your help so far guys. hopefully I will get soon!

 

[craigey]

it may be easier if someone can e-mail me an example of an access database doing what I'm trying to get mine to do.

the email is freemandc@yahoooooo.com

take out the oooo for the e-mail to work.
thanks guys for all your help!

 

[KenReay]

Hi

The code as per the site I gave you is:

Age=DateDiff(&quot;yyyy&quot;, [Bdate], Now())+ _
Int( Format(now(), &quot;mmdd&quot < Format( [Bdate], &quot;mmdd&quot )

You code looks the same to me, but you say:

&quot;the code is in the default value box and is as below.


=DateDiff(&quot;yyyy&quot;[Birthdate],Now())+Int(Format(Now(),&quot;mmdd&quot<Format([Birthdate],&quot;mmdd&quot) &quot;

I assume this is in a control on aform, in which case it should be in the rowsource, not the default

The enable / locked properties are properties of the control on the form.

You are using a form I take it?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[craigey]

i wasn't using a form. I think that's where my problem lies! I'll let you know how i get on. But I did mention in my first post to be thorough and on my second post did ask for step by step instructions!!!!


Always assume the user know nothing!

Thanks again!

 

[craigey]

I got it working!

Although I'm a little unsure about the code building. I added the code as mentioned by MichaelRed, but I was unsure as to how to call the code. But maybe that's for another post! I got the age function working and that's the main thing.

 

[CosmoKramer]

Add a text box with this as the control source:

=(Int((Date()-[DateOfBirth])/365.25))

 

[craigey]

The code works perfectly.

One more question though. If the date of birth is ahead of the current date the age is shown as -1. How can I code the validation to bring up an error if age is less then 12?

Thanks

 

[CosmoKramer]

In the After Update event of your DateOfBirth text box, put code something like this:

Dim BirthDate As Date
BirthDate = Date
If Me.txtDOB > BirthDate Then
   MsgBox ("Date of Birth cannot be after today")
   Me.txtDOB.SetFocus
End If

Also, here's a little enhancement on the code I gave you earlier. It will return the number of months for an age if the person is less than one year old:

=IIf((Int((Date()-[DateOfBirth])/365.25))<1,(DateDiff(&quot;m&quot;,[DateOfBirth],Date())+(((Format(Date(),&quot;dd&quot;))<(Format([DateOfBirth],&quot;dd&quot;))))) & &quot; months&quot;,(Int((Date()-[DateOfBirth])/365.25)))

 

[craigey]

I couldn't get
=IIf((Int((Date()-[DateOfBirth])/365.25))<1,(DateDiff(&quot;m&quot;,[DateOfBirth],Date())+(((Format(Date(),&quot;dd&quot)<(Format([DateOfBirth],&quot;dd&quot)))) & &quot; months&quot;,(Int((Date()-[DateOfBirth])/365.25)))

to work, I changed the IIF to IF, but still no luck. It's not a problem though as shouldn't have anyone of less then 12 in the db.

By the way I got the code woking about the Age -1 and from that I managed to figure out how to get an error returned if the age is below 12 or above 23.

I feel a great sense of satisfaction!!