Query age based on a calculation

[Roaming_Tigerr]

Hello peeps, hope someone can help?

Access 2007

I have a field 'Age' that automatically derives the age from another field 'Date of Birth.' However when I create/run a query where I want to show the age relevant to each record the age field is blank? I suspect it may be something to do with the fact that the age field is an automatic calculation rather than a numerical entry. I would though very much like to perform the simple task:banghead: of showing the age per field.

I hope:
a - you understand the question?
b - you can help as I haven't that many years leftD

Many thanks in anticipation
Mike

 

[vbaInet]

You're obviously using a function to perform the "Age" calculation. Did you include this function in your query too?

 

[Bryan]

I can't tell for sure, but it sounds like you may be calculating the age and storing it in a table, and then you are running a query that you want to display the age. I'm guessing that because you say your are able to derive the age but then run into problems with the query. If that's the case, your approach isn't correct.

You should not be storing the age in your table. Instead, just calculate it in your query. I think you may be having problems because you're trying to query a stored, calculated field.

If that isn't the case . . . disregard. As VBAInet suggested, post how you are calculating age.

 

[Roaming_Tigerr]

Many thanks to VBA & Bryan.

The field 'Age' in the form is a calculated field with the following in the Control Source of the Property Sheet:

=DateDiff("yyyy",[Date of Birth],Now())+Int(Format(Now(),"mmdd")<Format([Date of Birth],"mmdd"))

From that it calculates the difference between the data entered in the 'Date of Birth' field and today's date and then automatically enters a figure in the 'Age' field. e.g. 48

My issue is that when I create a query based on the Table (Contacts) a range of data (Fname/LName etc., all the fields show the data expected except the 'Age' field which is blank.

Bryan I think you may be closest to answering the problem but I have never performed a calculation with a query so would appreciate a few pointers if you have the time.

Many thanks in anticipation.

Mike (still :banghead

 

[vbaInet]

Like previously asked -->

Did you include this function in your query too?

You need to drop that formula in a column of your query that is based on your table.

 

[Brianwarnock]

Just to be clear your query will have a column

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

Brian

 

[Roaming_Tigerr]

Thank you all for your contribution/s - I think I've got the gist of it so will spend the weekend Querying til my heart's content. you have turned me from :banghead: to in less than a day - quite an achievement.

Thanks again
Mike