Query for age

[fsmith]

My database contains date of birth. I need to create a query that will find all members who are turning 65 or 26 in December this year. Can someone give me step by step instructions on how to create this query?

 

[hkimpact]

I am still pretty new at access myself, but I think you could do something like this.


Click the Query Design > Add your table that contains your Date of Birth field. In the column next to field type something similar to this

Age: Year(Now())-Year([DOB]) ' Replace [DOB] with your field for date of birth

In the Criteria you could put an OR statement that determines the age you are looking for..
"26" or "65" ' Type it exactly like that in the criteria field

 

[hkimpact]

I just tried this query and it works. But one thing is, is that this is an Approximate age. If you would like to calculate Accurate Age you could use this...

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

 

[Brianwarnock]

Nice try but that is still not an entirely accurate age calculation. There are a number of ways to do this but in your case I would use

Expr1: DateDiff("yyyy",[bdate],#1/12/2012#)+Int("1201"<Format([bdate],"mmdd"))

for the first calculation, and change the dates for the second

Expr2: DateDiff("yyyy",[bdate],#31/12/2012#)+Int("1231"<Format([bdate],"mmdd"))


What you need to do is chack that the person was under 65 or 26 on the first of December and Equal to it on 31st so on row 1 of the criteria put <65 under expr1 and 65 under expr2
repeat for 26 on row 2.

This gives a where clause, replacing the calculation code by fieldname for brevity
Where (expr1 <65 and expr2 =65)or(expr1<26 and expr2 =26)


Brian

 

[hkimpact]

I didn't read all his question apparently . Makes sense though Brian.

 

[fsmith]

I am still pretty new at access myself, but I think you could do something like this.


Click the Query Design > Add your table that contains your Date of Birth field. In the column next to field type something similar to this

Age: Year(Now())-Year([DOB]) ' Replace [DOB] with your field for date of birth

In the Criteria you could put an OR statement that determines the age you are looking for..
"26" or "65" ' Type it exactly like that in the criteria field

When you say 'in the column next to field . . . where do I click? There are cells labeled Field / Table/ Sort/Show/ Criteria / or. I am brand new to access and am figuring this all out as I go.

 

[fsmith]

Do I need to figure out how old the members are before I create the query?

 

[Brianwarnock]

Nope thats not quite true. His birthday must be in December, and on the 31st he must be either 26 or 65, to check his birth month Month(bdate) will suffice.

Brian

 

[fsmith]

Nope thats not quite true. His birthday must be in December, and on the 31st he must be either 26 or 65, to check his birth month Month(bdate) will suffice.

Brian

I need to know if the member will turn either 26 or 65 in December.

 

[Brianwarnock]

That's what I am doing, check his birthday is in December and if he is either 26 or 65 on the 31st, if so he must have reached that age in December.

Field     bmonth: Month(bdate)   Age: use calculation expr2 above
criteria     12                              65 0r 26

Replace bdate with you field name

Brian

 

[fsmith]

I see two columns in your previous response. I have one. It has the date of birth. do I need to add two more columns in my query? Access keeps saying it needs a destination. . .

 

[boblarson]

needs a destination. . .

Are you sure your query type is a SELECT query and not an update or append query? Also, did you put the formula in the FIELD area - NOT in the criteria. The Criteria would be

65 or 26

or

In(65,26)

 

[fsmith]

No I am not sure the query is 'select' or anything like that. Did I set up the query wrong?

 

[boblarson]

Did I set up the query wrong?

Don't know how you did it (remember we can't see what you see so you have to describe things to us thoroughly) so I can't say.

How did you do it?

 

[fsmith]

I used the query wizard to create a query that contains date of birth from my database. It did not asked me to specify "select".

 

[boblarson]

Post the SQL of the query. To get that, open the query in design view and then on the DESIGN tab and the Results Group, select VIEW > SQL VIEW and then copy and paste that here.

 

[fsmith]

SELECT [MUST MEMBERS].[Date of Birth]
FROM [MUST MEMBERS];

 

[hkimpact]

@FSmith - I'm going to attach a screen shot of Brian's example for you. I used age 47 and 29 as my example birth dates.

@Brian - correct me if I'm wrong, but the date format is dependent on how you have your date format set up in your table, correct?

 

[Brianwarnock]

Yes the date format in the design grid is in your local format, you can see that I am in the UK so use that format automatically, I don't know fsmith' location, in SQL OR VBA you must use US format.

Note that I have abandoned my first attempt as I realised it was wrong.

I think that we have a problem in that fsmith is not just new but has not even read a book or looked at an existing Access DB

Brian

 

[ecop1]

Thank you very much for this help.