displayin current age in a table field

[plog]

Field: Age: AgeYears([Date of Birth])

When you create a calculated field the name of the field is preceded by a colon. You have 2 colons, thus 2 names, thus error.

Remove one of the names from that query:

CurrentAge: AgeYears([Date of Birth])

 

[jdraw]

As others have said, store DateOfBirth in your table, then calculate Current age by means of a function. DateOfBirth does not change, but Age may change depending on the calculation date.
You can always calculate Age from DateOfBirth. Don't store values that can be calculated.

Good luck with your project.

 

[tadgh]

This is what i get when i copy paste doc mans code into query in age field

"The expression is typed incorrectly, or is too complicated to be evaluated. For example, a numeric may contain too many too many complicated elements. Try simplfying the expression by assigning parts of the expression to variables"

This is what is contained in the query field
Age: Int((CDbl(Now())-CDbl([Birthday]))/365.25)

someone please help !!!!!

 

[The_Doc_Man]

Int( ( CDbl( Now() ) - CDbl( [Birthday] ) ) / 365.25)

My typing is sometimes a bit wonky. Here is how you could analyze this to assure it is set up correctly. Work from inside to outside.

Start by computing the number of days difference between the dates:

CDbl( Now() ) - CDbl( [Birthday] )

Now convert to years and fractions:

( CDbl( Now() ) - CDbl( [Birthday] ) ) / 365.25

Now lose the fractions:

Int( ( CDbl( Now() ) - CDbl( [Birthday] ) ) / 365.25 )

If that doesn't work in the query then something isn't defined correctly, and the only thing I can see that might be questioned is [Birthday]. Is there a field of that type and name in the underlying table that drives this query? Is "Birthday" a text field or a date/time field?

If it is actually a text field, then the above has to become

Int( ( CDbl( Now() ) - CDbl( CDate( [Birthday] ) ) ) / 365.25 )

Using Age: in the query is correct, by the way, so I don't see an issue there.

 

[Brianwarnock]

Now, as to the module, which is certainly correct: There is another way to do this that might be easier to write in a query without needing a module:

Int( ( CDbl(Now()) - Cdbl(Birthday)) / 365.25 )

I think the ONLY time this will be wrong (and it will be wrong only one day per leap year) would be on Feb 29th of a leap year for someone whose birthday was Mar 1st of a non-leap year. Note also that if you happen to be computing the age of Methuselah, the correct fraction is 356.2422.

This works because dates are internally expressed in the system as DOUBLE-format real numbers that are the difference in days and fractions thereof since the system reference date. The above formula computes the difference between now and the birthday as a number of days and fractions, then divides that by the number of days in a year, then truncates it. (Do NOT use a rounding function for this purpose!)

For someone older than, say, 15 years old, the odds are pretty good for this to be right 99.93% of the time. The older the person is, the better the odds get.

I am sorry doc but this is fundamentally flawed, a person born 1/1/15 will be 1 year old on 1/1/16 but that is only 365 days thus your calculation will return 0, thus it is incorrect on all birthdays for three out of four years , below is the correct calculation

Age: Datediff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")<Format([Date of Birth],"mmdd"))


Brian

 

[plog]

I am sorry doc but this is fundamentally flawed, a person born 1/1/15 will be 1 year old on 1/1/16 but that is only 365 days thus your calculation will return 0,

In the spirit of being a pedant myself and further derailing this thread without actually helping tadgh, allow me to point to the very narrow way Brian is incorrect.

Your example of Doc's code being wrong on January 1 for someone born on January 1 is wrong as well. At around 6 am on January 1 (god I hope I am off by a few hours so someone else can out pedantic me) Doc's code will actually be right. So, for the majority of January 1, Doc's code will return 1.

I eagerly await corrections to my correction of Brian's correction of Doc's code.

 

[Brianwarnock]

LOL yes I didn't allow for the fact that used NOW() not DATE() so maybe you are correct, in fact you could follow the trend if we start in 1/1/13 then in 14 it is 6 am , in 15 it is 12 am , in 16 it is 6 pm and in 17 it is correct, then we repeat the cycle.

But let's do it correctly as per my code heh!

Brian

 

[The_Doc_Man]

All of which shows that we ALL learn something by looking at the way other people look at a problem. Which actually was one of my talking points.

 

[The_Doc_Man]

As to the other point... I also said that it got more accurate the older you got. For someone 1 to 3 years old, the hour of the day makes a difference. However,

The number of days between 1/1/2011 to 1/1/2012 is, indeed, 365 until about 0600. As often happens, I think in Sidereal time when the question was about calendar time. Oh, well.

You could always try this:

Int( ( 1 + ( CDbl(Now()) - Cdbl(Birthday) ) ) / 365.25 )

That way, it eliminates the problem that time-lines are ZERO-based - which was my actual oversight. (Proof of the point: If you were born on 1/1/2001, how old are you on 1/1/2001?)

 

[Minty]

What happens if you're 21 again ?

 

[plog]

As to the other point...

Still missing my main point, see post #23: http://www.access-programmers.co.uk/forums/showpost.php?p=1465034&postcount=23

 

[Minty]

Still missing my main point, see post #23: http://www.access-programmers.co.uk/forums/showpost.php?p=1465034&postcount=23

I'm old, so
date()- (birthdate + txtQuiteABit)= preferred.age ?

 

[plog]

Ok, let me explicitly state it:

While everyone was trying to outcode each other and get age perfect to 9 signficant digits, helping tad fell through the cracks. His last post mentioned a syntax error which no one helped him with.

Tad are you still having an issue? Confused about which of the 800 snippets of codes we've given you? Have been able to successfully generate an age for your data?

 

[Brianwarnock]

Plog
DocMan addressed tad's post in the very next post.

Is it wrong to correct a flawed formula?
That people jumped through hoops to say it was not totally wrong was either a bit of fun or stupid, it is wrong , end of story. the formula I gave is 100% correct 100% of the time, and yes there are other ways to achieve it, all are based on checking to see if there has been a birthday and modifying the years if not.

Brian

 

[The_Doc_Man]

What happens if you're 21 again ?

My wife would meet me at the door, say "Who the hell are you and what did you do with my husband?" and then call the cops. I haven't been 21 (originally or again) in over 45 years.

 

[The_Doc_Man]

As to the syntax error:

Int( ( 1 + ( CDbl( Now() ) - Cdbl( Birthday ) ) ) / 365.25 )

When I take that string into Debug.Print and use the literal string #18-Feb-1948# in place of the word "birthday" , it does not give me a syntax error. The only two things that I could see not working would be if the wrong number of parentheses were used or if there is something wrong with the birthday string. All I can offer is that if you wanted to try my solution, count the parentheses carefully. However, I stand by that formula as being syntactically correct if given a valid date for the birthday string.

If you wish to quibble about the minor technicality of sometimes not being correct, ... well Jeez Louise. You want good syntax and accuracy too? By the way, it is still syntactically correct if you wish to use Date() rather than Now().


PLog, I understand we are trying to solve tadgh's problem. I offered a non-VBA solution. Others have offered their solutions. However, I did not ignore the complaint about syntax errors. I can't reproduce the problem.

 

[tadgh]

thanks plog thats exactly it, all doing battle and ignoring the plea lol ,,,i keep getting syntax error for that code i am going to try some of the others just have not had a moment to do so yet, yes even i recognise their are many ways of approaching any problem in life but for now i just need questions answered, specific questions !!! like why am i getting that syntax error and how do i fix it . when i see the reply i can look at the original and look at the new and hopefully learn why it would not work.

 

[tadgh]

thank you all for your help, brianwarnock your code has worked for me thanks a million really appreciate it dude

 

[enriquegill012]

Don't get bound up on having tables display anything other than raw data. You can write queries to drive forms because (here's the key thing I want you know) both tables and queries supply RECORDSETS and all of the Access components work with RECORDSETS.