Age query

[JPR]

Hello,

I have a table with the list of my clients. A field of this table is their Date of Birth (DOB). I would like to create a query that returns the list of clients that in the current year will obtain a specific age (example 65). I will select the criteria (the age) from a combo.

Thank you

 

[theDBguy]

Hi. Add a calculated column in your query to calculate the age and use a reference to the combobox as a criteria.

 

[JPR]

Thank you for your quick reply.
The problem is that I don't know what to write in the calculated field.

Will appreciate any additional help.

 

[Gasman]

Try Google

https://superuser.com/questions/1178370/how-do-you-calculate-age-from-a-birth-date-in-ms-access

 

[Minty]

This isn't quite as straightforward as you might think.

Have a read here for the how's and why's http://allenbrowne.com/func-08.html

This gives you a function you can use simply in your query.


Edit: just because you can, you could type this lot into your query editor;

AgeNow: IIf(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))>Date(),DateDiff("yyyy",[DOB],Date()),DateDiff("yyyy",[DOB],Date())-1)

 

[JPR]

Thank you for your replies.
Still not sure if I got it clear.
My combo lists the age (1,2,3,4,ect, up to 100).
When I select the age, the query should return all records that based on the date of birth, will turn the age selected in the combo, in the current year.

Example: The value selected in the combo is 100.

The query should return all clients who will turn age 100 in the current year (01/01/1919 - 12/31/1919) searching this range in the DOB field.

Thank you and sorry for not being clear enough.

 

[Minty]

Okay so add the calculated field described above. (copy and paste it into the top line (field) of the query editor) .

Underneath in the criteria section for that new field, add a reference to your forms control, something like.

[Forms]![YourFormName].[YourComboName]

Replacing the form and control with the correct ones for your form.

You may want to fiddle with the expression I used to get everyone this year who will be a hundred.
Allen Brownes site should help with that , but post back if you get stuck.

 

[theDBguy]

Thank you for your replies.
Still not sure if I got it clear.
My combo lists the age (1,2,3,4,ect, up to 100).
When I select the age, the query should return all records that based on the date of birth, will turn the age selected in the combo, in the current year.

Example: The value selected in the combo is 100.

The query should return all clients who will turn age 100 in the current year (01/01/1919 - 12/31/1919) searching this range in the DOB field.

Thank you and sorry for not being clear enough.

Hi. Can you post the SQL statement for your query now returning all the information you need from the table? We'll try to add the criteria for you.

 

[Mark_]

I think I see what your may really be asking for;
Year(Dob) will return the year they were born.
In your query, you would have something like

YOB: Year([DOB])

Thus your logic would be

YOB = (Year(date()) - NZ([Forms]![YourFormName].[YourComboName] ,0)

to return everyone who will be that age in a given year.

YOB >- (Year(date()) - NZ ([Forms]![YourFormName].[YourComboName] ,0)

returns everyone who will be that age or older.

 

[JPR]

Than you for your help.
I have been able to add the YOB field in my query but not the other ones.
I have uploaded a simple example of my db which will probably help you to have an idea of my confusion.

Thank you. Really appreciate your help.

 

[Minty]

Okay so in your sample there is no combo with a age in it, do you want to add that to further filter the results ?

 

[JPR]

Yes, possibly to the same cbo. Thank you

 

[Minty]

Looking at your combo source I think you are trying to do something quite complicated, which is to use the same combo to filter the results in two seperate ways. One by date one by payment status.

Whilst not impossible, this isn't combo box 101 for learners.
Normally you would use two combo's one for the payment status and one for the age. This may be why you are struggling.

I'll try and knock up a sample from your db.

 

[Minty]

Actually I started doing this and realised I was re-inventing the wheel. The almost definitive search form and examples on how to do it are here http://allenbrowne.com/ser-62.html

With a really clear explanation of how it works and why.