Age calculation help needed 1

[techexpressinc]

I have a DOB on the database.
Table = Members
Field = DOB

I seen this as a formula - “AgeCalcField: AgeInYears([DOBField], Date())” -


Within my query I do this
I click the field box
and coded an expression as listed here and get a syntax error:

“AgeCalcField: AgeInYears([MembersOB], Date())”


Can someone help in getting the age on my query working?

Thanks a lot - Russ @ scaninc.org

 

[dhookom]

There is not an Access standard function AgeInYears(). You need to create (or borrow) a function or expression. There is a good reference at Calculate Age of a person Here is an expression from the page:

 AgeCalcField:DateDiff("yyyy", [Members:DOB], Now())+ Int( Format(now(), "mmdd") < Format( [Members:DOB], "mmdd") )

Duane
Hook'D on Access
MS Access MVP

 

[techexpressinc]

Thanks - No luck yet -

I did the above code in my field box and goe the pop-up

"Enter Parameter Value
MembersOB
"

Russ

 

[HarleyQuinn]

It was copied from your post!

Put the name of your dat of birth field in instead.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[techexpressinc]

That is my field name for date of birth.
table name = "Members"
field name = "DOB"
Russ

 

[Dontremb]

You could always use this:

Age: (Date()-([DoB])/365.25

This will give you an accurate age. (Though it IS possible that it can be off by one day from time to time.)


This one is more accurate:

DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd"))

(I just checked this one with a query, and it works perfectly.)

Let me know if it doesn't work as you want it to.

 

[HarleyQuinn]

Russ,

It should work if you spereate them with a . rather than a :

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Dontremb,

Isn't that the same call as Duane posted?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Dontremb]

HarleyQuinn,

The basic call is the same. I just didn't incorrectly implicitly label his DOB field. [MembersOB] should be [Members.DOB]

But also, I don't think it's necessary to cite the table, is it?

 

[HarleyQuinn]

That was the way the OP had labelled his DOB field in the later part of the original question. (but it should most likely be [Members].[DOB])

Correct, it's not necessary to cite the table, I generally always do though just because I've been used to working on pretty bad DB's where it allows clear distinction between fields so there is not ambiguity, plus if you added fields in the designer in Access it will add them so it helps with keeping all your query looking the same.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[techexpressinc]

I am trying and trying:

My lastest is:
"Age: (Date()-([Members.DOB]))/365.25"
in the field box
and I am still getting an error a Complie error in query expression '(Date () - ([Members.DOB))/365.2'.

Thanks for following thru this mess.
Russ

 

[HarleyQuinn]

Please don't use the less accurate expression, try:

 AgeCalcField:DateDiff("yyyy", [red][Members].[DOB][/red], Now())+ Int( Format(now(), "mmdd") < Format( [red][Members].[DOB][/red], "mmdd") )

Does that work for you?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Dontremb]

Ah, thank you for the clarification, HarleyQuinn.

I am completely self-taught Access, with only a few MS Office Online tutorials, so I'm sure I don't do everything according to the best conventions.

My current database is, in fact, my first one to attempt to use proper naming conventions. (ie: tblTable, frmForm, and also using underscores for field names instead of spaces.)

I'm interested to see if your last post fixed the author's problem. I probably would have missed putting brackets individually around the table, AND the field, as well.

 

[HarleyQuinn]

Dontremb,

No problem. What I really should have explained better in my last post is that the only real reason that the bracketting is needed is for tables/fields with spaces in, I don't think I mentioned that in my last post.

My current database is, in fact, my first one to attempt to use proper naming conventions. (ie: tblTable, frmForm, and also using underscores for field names instead of spaces.)

Nice one, it's much easier to work with and it does become second nature pretty quickly

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[dhookom]

Dontremb,
I am also self-taught in Access as are most others. I don't know about others, but I have never made any programming mistakes ;-)

Naming conventions are a gift to programmers. I find a good convention removes the "shopping around" for names for stuff. Naming becomes fairly mechanical at some point.

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

I don't know about others, but I have never made any programming mistakes

Me EITHER!!! We are awesome!

Les

 

[PHV]

I personally wonder what a programming mistake could be <grin>

 

[techexpressinc]

Still failing
please take a look at the doc at the link it is snapshots trying to make my failure more clear.

Thank you
Russ

 

[dhookom]

Do you have any DOB fields that are empty? Is the DOB field an actual date data type?

This also might be an issue with Access 2007 security settings. I don't have enough experience with it to know for sure.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Your join fields don't make sense. Something must have changed somewhere since you have a [MemberID#] field joined to [Recipient F Name]. What's up with that?

Duane
Hook'D on Access
MS Access MVP