date of birth/todays date = age in yrs/mths? HOW

  • Thread starter Thread starter Paul Saunders
  • Start date Start date
P

Paul Saunders

I have a simple database with names and DofB's. I want
to arrive at an automatic age in yrs/mths displayed as a
querey, how on earth do I do this?

example: list all people between the ages 15-16 yrs

joe bloggs DofB 15 Aug 86 (already inputted in a table)
Todays date 21 Jul 03 (the computer knows the date!!)

WANTED .... A querey button on a form that calculates
that joe Bloggs DofB (amonst others) falls in to the age
button on the front page of the database?!?

Any help would be appreciated

Paul Saunders
 
Hello,

There is a function named datediff. If you supply it with two dates and a
comparison unit (i.e. months or years or days) it will return the difference
in units of the two dates. I guess one date would be your DoB and the other
would be the date on your form. Have a look at the help for this function as
I think it'll be what you're after.

Cheers,

<M>ike
 
WANTED .... A querey button on a form that calculates
that joe Bloggs DofB (amonst others) falls in to the age
button on the front page of the database?!?
Click to expand...

I have NO trace of a clue what you mean by "falls in to the age button
on the front page"...

but the query expression you want is

DateDiff("m", [DofB], Date()) \ 12 & " yr. " & DateDiff("m", [DofB],
Date()) MOD 12 & " mo."
 
1. I have a db form that i enter a date of birth on to,
amonst other details like name etc..
Click to expand...

I presume that this data is stored in a Table? (Note: a Form is JUST A
WINDOW. The table is fundamental).
2. when I enter the DofB I would like an age in yrs/mths
to appear in another box on the same form.
Click to expand...

Set the Control Source property of that textbox to

=DateDiff("m", [txtDofB], Date()) \ 12 & " yr. " & DateDiff("m",
[txtDofB], Date()) MOD 12 & " mo."

(assuming that the textbox into which you are entering the date of
birth is named txtDofB).
3. I would also like a querey button on another form
that allows me to view the people in my database split in
to age groups i.e. 13-14yrs, 14-15yrs,15-16yrs etc.
Click to expand...

This has nothing to do with your original question, of course...

Just what do you mean by "a query button"? a Button control calls a
VBA function or a macro - it's not a query. How do you want to "view"?
would you like a continuous form showing the age group, or would you
like to filter the table to show just a specific age group, or what?
 
Hi again

I tried what you suggested to get the age thing come up,
I guess im not cut out for this kind of thing over the
internet. Im sorry I am thick or something, its just a
little too confusing for me.

1. I renamed the date of birth box to txtDofB as requested
2. I opened a text box and wrote in the control source
the command line you so kindly provided
3. Was i supposed to call the new text box a specific name
4. are you sick of me yet?
5. do you live anywhere near Dartford, Kent. UK?
6. The txt box I opened and typed in that line called
itself UNBOUND
7. Was I supposed to put that command line in to the
expression builder?


Paul Saunders





-----Original Message-----
-----Original Message-----
1. I have a db form that i enter a date of birth on to,
amonst other details like name etc..
Click to expand...

I presume that this data is stored in a Table? (Note: a Form is JUST A
WINDOW. The table is fundamental).
2. when I enter the DofB I would like an age in yrs/mths
to appear in another box on the same form.
Click to expand...

Set the Control Source property of that textbox to

=DateDiff("m", [txtDofB], Date()) \ 12 & " yr. " & DateDiff("m",
[txtDofB], Date()) MOD 12 & " mo."

(assuming that the textbox into which you are entering the date of
birth is named txtDofB).
3. I would also like a querey button on another form
that allows me to view the people in my database split in
to age groups i.e. 13-14yrs, 14-15yrs,15-16yrs etc.
Click to expand...

This has nothing to do with your original question, of course...

Just what do you mean by "a query button"? a Button control calls a
VBA function or a macro - it's not a query. How do you want to "view"?
would you like a continuous form showing the age group, or would you
like to filter the table to show just a specific age group, or what?


.
Click to expand...
Hello again,

As you can tell Im no expert, however, I have limited
knowledge as you have found out already!

I shall try to explain, I guess what i am after is a
filter, I need to have an option to print out all the
people in specific age categories, as mentioned in my
previous email. If I was barking up the wrong tree about
something I thought was linked then I am sorry! On my
front page of the db, I need to click my mouse on a button
(laymans term) and have a result that can let me view
these people in the age groupings, does that make sense?

The reason for asking about the "datediff" thing was
because I thought you needed to setup a routine for the
computer to work out the ages, then get the computer to
extract the people in the different age groups. Can you
see my logic!

Paul
.
Click to expand...
 
Hi, sorry, I studied your last posting and tried again...
it worked ... you knew it would!!

excellent job, many thanks for that bit.

as for the listing the age groups ....

1. I now realise that when I say button, you think im
talking technical! On my front page of the db I have
various quereies to tell me the state of their education
(Air Training Corps, a bit like the C.A.P. if you are in
the States!)

2. What I want is an icon on the front page that can tell
me how many people there are within certain age brackets,
these age brackets are 13 & over, 14 & over, 15 & over,
16 & over, 17 & over, 18 and over (top age of cadets is
22 yrs)

3. So as you can see it splits these people into certain
age categories.

4. As for viewing the results, it doesnt really matter,
as all I have to do is put a number in a box, I just
remembered that it wants to know the numbers in each age
cat., but with separate numbers for how many boys and how
many girls.

5. Seriously, if youve had enough of this please let me
know I will stop bugging you.

Paul Saunders
 
2. What I want is an icon on the front page that can tell
me how many people there are within certain age brackets,
these age brackets are 13 & over, 14 & over, 15 & over,
16 & over, 17 & over, 18 and over (top age of cadets is
22 yrs)
Click to expand...

Just create a small Table with three fields: Low, High, and Rangename:

13 14 "13 & over"
14 15 "14 & over"
....
18 20 "18 & over"

As you can see this gives you the flexibility to construct ranges that
differ from a single year.

Add this table to a Query based on your table, with no join line, and
use a criterion on Low of

<= DateDiff("yyyy", [DOB], Date()) - IIF... <from previous post>

and on High of
DateDiff("yyyy"...
Click to expand...

Display this query in a Subform on your form.
 
Can I clarify..

Just create a small Table with three fields: Low, High,
and Rangename:

13 14 "13 & over"
14 15 "14 & over"
.....
18 20 "18 & over"

1. create a table in design view
with three fields called "Low" "High" & "rangename".
What do I do with the 13 14 "13 & over" lines you have
typed. After creating these fields it asks if I want to
create a primary key ... I presume no is the answer!

I understand about the flexibility in the range names you
spoke about.

you lost me on this bit -

Add this table to a Query based on your table, with no
join line, and use a criterion on Low of

<= DateDiff("yyyy", [DOB], Date()) - IIF... <from
previous post> - ??????????

and on High of
DateDiff("yyyy"... ????????
Click to expand...

Display this query in a Subform on your form.

sorry
Paul
 
Can I clarify..

Just create a small Table with three fields: Low, High,
and Rangename:

13 14 "13 & over"
14 15 "14 & over"
....
18 20 "18 & over"

1. create a table in design view
with three fields called "Low" "High" & "rangename".
What do I do with the 13 14 "13 & over" lines you have
typed. After creating these fields it asks if I want to
create a primary key ... I presume no is the answer!
Click to expand...

Correct; though you could, if you wish, ctrl-click Low and High and
then the Key icon to make the two fields a joint Primary Key.

Just open the new table and put numbers in Low and High and text
strings in Rangename.
I understand about the flexibility in the range names you
spoke about.

you lost me on this bit -

Add this table to a Query based on your table, with no
join line, and use a criterion on Low of

<= DateDiff("yyyy", [DOB], Date()) - IIF... <from
previous post> - ??????????
Click to expand...

Sorry! Forgot that I'd given you a years-and-months value. Use

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date()), "mmdd"), 1, 0)

as a criterion.

If you would ever want to have ranges like 12 1/2 years to 14 years 3
months (just speculating here) you would want to use age in months as
the Low and High (150 to 171 in this example) and use DateDiff("m",
[DOB], Date()) in the criteria.
 
Back
Top