By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Help in MS Access

P: 3
Hi,

I would like to be able to view a person's age in a query field called "Age" by having the person's date of birth in an adjacent field called "Date of Birth".

Would you kindly walk me through this process step by step from the creation of the query to actually running the results. If there is a formula to use, where do I place that formula exactly?

Thank you.
Nicole
Jul 24 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi,

I would like to be able to view a person's age in a query field called "Age" by having the person's date of birth in an adjacent field called "Date of Birth".

Would you kindly walk me through this process step by step from the creation of the query to actually running the results. If there is a formula to use, where do I place that formula exactly?

Thank you.
Nicole
Will be glad to help but I need additional info to guide you through query creation step-by-step as you've wished.
  • provide metadata of the table the query is based on
    Here is an example of how to post table MetaData :
    Table Name=tblStudent
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. LastAttendance; Date/Time
  • how would you like to see person age? years only, y/m/d etc ?
Jul 24 '07 #2

P: 3
Will be glad to help but I need additional info to guide you through query creation step-by-step as you've wished.
  • provide metadata of the table the query is based on
    Here is an example of how to post table MetaData :
    Table Name=tblStudent
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. LastAttendance; Date/Time
  • how would you like to see person age? years only, y/m/d etc ?
Hi FishVal,

The Name of the table I am using to create the query from is called....

Table Name=MemberAndRegularAttendee

I would like to see the person's age in years only.

I have created several queries, but I don't know where to put a formula, if there is one for calculating ages; I am working in Design view in the query. The rows named by access are displayed on the left : Field, Table, Sort, Show, Criteria, Or. I have selected several column headings from the table named above; one of those columns is "Date of Birth" another is name. What are the next steps I need to perform to have a column called "age" that displays age based on the "date of Birth" column compared to the current day's date that I am working with.

Thanks, I appreciate your help.

Nicole
Jul 30 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
This formula gives the accurate age in years i.e. it takes into account whether or not the person has had their birthday for the current year.
Expand|Select|Wrap|Line Numbers
  1. AgeToday = DateDiff("yyyy", [Birthday], Date) - IIf(Format$(Date, "mmdd") < Format$([Birthday], "mmdd"), 1, 0)
  2.  
I'll let someone with more expertise in using formulas in SQL help you with that part of the problem.

Welcome to TheScripts, Nicole!

Linq ;0)>
Jul 30 '07 #4

P: 3
This formula gives the accurate age in years i.e. it takes into account whether or not the person has had their birthday for the current year.
Expand|Select|Wrap|Line Numbers
  1. AgeToday = DateDiff("yyyy", [Birthday], Date) - IIf(Format$(Date, "mmdd") < Format$([Birthday], "mmdd"), 1, 0)
  2.  
I'll let someone with more expertise in using formulas in SQL help you with that part of the problem.

Welcome to TheScripts, Nicole!

Linq ;0)>
Can someone assist me regarding where to put the formula for calculating ages in access.

I have created several queries, but I don't know where to put a formula, if there is one for calculating ages; I am working in Design view in the query. The rows named by access are displayed on the left : Field, Table, Sort, Show, Criteria, Or. I have selected several column headings from the table named above; one of those columns is "Date of Birth" another is name. What are the next steps I need to perform to have a column called "age" that displays age based on the "date of Birth" column compared to the current day's date that I am working with.

Thanks, I appreciate your help.

Nicole
Jul 31 '07 #5

Expert 100+
P: 296
Can someone assist me regarding where to put the formula for calculating ages in access.

I have created several queries, but I don't know where to put a formula, if there is one for calculating ages; I am working in Design view in the query. The rows named by access are displayed on the left : Field, Table, Sort, Show, Criteria, Or. I have selected several column headings from the table named above; one of those columns is "Date of Birth" another is name. What are the next steps I need to perform to have a column called "age" that displays age based on the "date of Birth" column compared to the current day's date that I am working with.

Thanks, I appreciate your help.

Nicole
I have a query where I calculate the age based on the birthdate and this is how I did it:
In query design view, in the Field row of an empty column, type:
Age: Int(DateDiff("y",[DateOfBirth],Now())/365.237)
Jul 31 '07 #6

P: 48
Hi,

I would like to be able to view a person's age in a query field called "Age" by having the person's date of birth in an adjacent field called "Date of Birth".

Would you kindly walk me through this process step by step from the creation of the query to actually running the results. If there is a formula to use, where do I place that formula exactly?

Thank you.
Nicole

In query, type these code in the field. Remember to change "DateOfBirth" from my code below to the field name you used in your table. It works for me.Hope this help.

Expand|Select|Wrap|Line Numbers
  1. Age:DateDiff("yyyy",[DateOfBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateOfBirth],"mmdd"))
Sep 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.