Help using dates as in birthdates

[emandel]

I am an armature access programmer trying my best to design my own database so your patience and detailed instructions is much appreciated.

I have birthdates in my "participan ts" table

1) How do I set the criteria so that I only see people that are older than x
2) in a form, how do I add a calculated field so that it shows me how old the person is as of today?

[emandel]

Anyone out there to help?

[MMcCarthy]

I am an armature access programmer trying my best to design my own database so your patience and detailed instructions is much appreciated.

I have birthdates in my "participan ts" table

1) How do I set the criteria so that I only see people that are older than x

This would be done using a query. Assuming birthdate is the field name and age restriction is 35.

 
SELECT * FROM Tablename
WHERE DateDiff('yyyy', Now(), [birthdate]) > 35;

2) in a form, how do I add a calculated field so that it shows me how old the person is as of today?

Make the control source of the textbox

=DateDiff ('yyyy',  Now(), [birthdate])

[emandel]

Make the control source of the textbox

Code:
=DateDiff ('yyyy', Now(), [birthdate])

----------------------

Thank you
2 problems,

1) why is there a minus sign before the age?
2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)

[emandel]

This would be done using a query. Assuming birthdate is the field name and age restriction is 35.

Code:

SELECT * FROM Tablename
WHERE DateDiff('yyyy' , Now(), [birthdate]) > 35;

can this be done as a filter within the table under criterea?
How can it be done?

[MMcCarthy]

Make the control source of the textbox

Code:
=DateDiff ('yyyy', Now(), [birthdate])

----------------------

Thank you
2 problems,

1) why is there a minus sign before the age?

Try reversing the code as follows:

=DateDiff ('yyyy', [birthdate], Now())

2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)

No it will look at the dates and calculate the age based on all complete years.

e.g. based on dd/mm/yyyy formats

DateDiff ('yyyy', #12/10/2000#, #12/09/2006#) = 5

but

DateDiff ('yyyy', #12/10/2000#, #12/11/2006#) = 6

Mary

[MMcCarthy]

can this be done as a filter within the table under criterea?
How can it be done?

You cannot filter the table directly. Why can't you use a query to do it? Is there some requirement that isn't satisfied?

Mary

[emandel]

Try reversing the code as follows:

=DateDiff ('yyyy', [birthdate], Now())

Quote:
Originally Posted by emandel
2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)


No it will look at the dates and calculate the age based on all complete years.

e.g. based on dd/mm/yyyy formats

Code:
DateDiff ('yyyy', #12/10/2000#, #12/09/2006#) = 5


but

Code:
DateDiff ('yyyy', #12/10/2000#, #12/11/2006#) = 6

OK the minus sign is gone, but it is still giving me the wrong calculation.

I have my bithday as July 15 1973 which means that I am not 34 until July 15 2007, yet in the field that i just created it shows it as 34 as of now already. Any suggestons?

[MMcCarthy]

OK the minus sign is gone, but it is still giving me the wrong calculation.

I have my bithday as July 15 1973 which means that I am not 34 until July 15 2007, yet in the field that i just created it shows it as 34 as of now already. Any suggestons?

This shouldn't be happening.

Try replacing Now() with Date().

What format are you using in birthdate field for the date?

Mary

[emandel]

Try replacing Now() with Date().

What format are you using in birthdate field for the date?

That didn't work either,

The format for birthdate is general date

is there something else I should be looking at?

[MMcCarthy]

General Date should have worked with Now()

Try the following ...

 
SELECT * FROM Tablename
WHERE DateDiff('yyyy', Format([birthdate], 'Short Date'), Format(Now(), 'Short Date')) > 35;

and

=DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))

[emandel]

Try the following ...

Code:

SELECT * FROM Tablename
WHERE DateDiff('yyyy' , Format([birthdate], 'Short Date'), Format(Now(), 'Short Date')) > 35;


and

Code:
=DateDiff ("yyyy", Format([birthdate], "Short Date"), Format(Now(), "Short Date"))

Sorry do I put both of these codes in the control source of the "age" text box?


--------------------------------------------------------------------------------

[MMcCarthy]

Just this one ...

=DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))

[MMcCarthy]

Just a thought

Have you checked that your computers system date is correct?

Mary

[emandel]

My computer's system date is fine,

I am giving up for the night, it doesn't make any sense to me.

Thank you so much for your help over the last hour an d a half