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

Help using dates as in birthdates

emandel
P: 65
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 "participants" 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?
Jan 1 '07 #1
Share this Question
Share on Google+
29 Replies


emandel
P: 65
Anyone out there to help?
Jan 1 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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 "participants" 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM Tablename
  3. WHERE DateDiff('yyyy', Now(), [birthdate]) > 35;
  4.  
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

Expand|Select|Wrap|Line Numbers
  1. =DateDiff ('yyyy',  Now(), [birthdate])
Jan 1 '07 #3

emandel
P: 65
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)
Jan 1 '07 #4

emandel
P: 65
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?
Jan 1 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. DateDiff ('yyyy', #12/10/2000#, #12/09/2006#) = 5
but

Expand|Select|Wrap|Line Numbers
  1. DateDiff ('yyyy', #12/10/2000#, #12/11/2006#) = 6
Mary
Jan 1 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 1 '07 #7

emandel
P: 65
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?
Jan 1 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 1 '07 #9

emandel
P: 65
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?
Jan 1 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
General Date should have worked with Now()

Try the following ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM Tablename
  3. WHERE DateDiff('yyyy', Format([birthdate], 'Short Date'), Format(Now(), 'Short Date')) > 35;
  4.  
and

Expand|Select|Wrap|Line Numbers
  1. =DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))
Jan 1 '07 #11

emandel
P: 65
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?


--------------------------------------------------------------------------------
Jan 1 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Just this one ...

Expand|Select|Wrap|Line Numbers
  1. =DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))
Jan 1 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Just a thought

Have you checked that your computers system date is correct?

Mary
Jan 1 '07 #14

emandel
P: 65
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
Jan 1 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
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
No problem.

I'm going to ask someone else to look at this tomorrow.

Mary
Jan 1 '07 #16

Expert 5K+
P: 8,435
No problem.

I'm going to ask someone else to look at this tomorrow.

Mary
I'm heading off to bed, but I'll be looking forward to jumping into this thread in the morning, if it isn't resolved by then. Seems pretty interesting.

Plus, I have (surprise!) a suggestion to make. It seems as though it would be considerably more efficient to use DateAdd function rather than DateDiff to do this sort of job - assuming DateAdd is readily available in Access. Anyway, we can discuss this. (And of course, it won't really matter unless you're dealing with pretty large tables.)
Jan 1 '07 #17

NeoPa
Expert Mod 15k+
P: 31,186
Brilliant Killer.
I was coming to look and saw that DateDiff("yyyy",A,B) actually gives the equivalent of Year(B)-Year(A). I was just starting to think of a way around it when up you come with DateAdd. Thinking Out-Of-The-Box again.
So :
1) How do I set the criteria so that I only see people that are older than x?
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE DateAdd('yyyy',35,[birthdate])>Date();
NB Older here can mean as little as 1 day older. Either say >= for as old or older than x or use >=x+1 for sensible results.
2) in a form, how do I add a calculated field so that it shows me how old the person is as of today?
Expand|Select|Wrap|Line Numbers
  1. =Int(Format(Date(),"yyyy.mmdd")-Format([Birthday],"yyyy.mmdd"))
Jan 1 '07 #18

emandel
P: 65
Code:
=Int(Format(Date(),"yyyy.mmdd")-Format([Birthday],"yyyy.mmdd"))
That did it!

Thank you!
Jan 1 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
That did it!

Thank you!
You sound surprised :(.
Seriously - pleased it works for you :).
Jan 1 '07 #20

emandel
P: 65
As i stated right on the top, I am a complete novice to programming and to databases, I understand very clearly what I need but no idea of how, I am doing this all with the help of books, onine help, and now most of all to all of you!
:)
Jan 1 '07 #21

Expert 5K+
P: 8,435
Brilliant Killer.
Thank you. :)
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE DateAdd('yyyy',35,[birthdate])>Date();
No, no, no!

Ok, if this produces the correct result, that's great. But I pointed out DateAdd as a way to avoid having to process every record.

If you think about it, applying a function to the database field means that every value must be read (whether it is indexed or not) and run through the function to determine whether the record is wanted. The idea is to apply the (reverse) function to the parameter value so that (hopefully) it only needs to be performed once, and then the record selection based on the resulting value.

How about something like...
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE [birthdate] < DateAdd('yyyy',-35,Date());
Jan 1 '07 #22

NeoPa
Expert Mod 15k+
P: 31,186
I hate to admit it (and you maybe made more of a deal of it than it's worth) but you're absolutely right.
Your way is certainly better performance-wise.
I would go as far as to say I wish I'd thought of that.
Jan 1 '07 #23

emandel
P: 65
I had two original questions

My second question was on each form I wanted a field that calculated the age regardless of how old they are. That was where I was getting a year older each time. NeoPa's code worked in the control source for that field (age)

Regarding my first question, I would like to make a query and say everyone who is younger than 40 for example. I am not up to that yet but I am glad that I got out of the other problem.

I am going to be starting a new thread for how to arrange my quary the way I want it. I think that it is complicated.
Jan 1 '07 #24

Expert 5K+
P: 8,435
I hate to admit it (and you maybe made more of a deal of it than it's worth) but you're absolutely right.
Your way is certainly better performance-wise.
I would go as far as to say I wish I'd thought of that.
Thank you. :)

I'm aware that this sort of consideration is entirely trivial unless you are dealing with BIG tables. However, I feel it's important to develop the habit of looking for the more efficient method, for those times when it does become significant.

At work, there was a report (to pick my favourite example) which regularly took almost two days to run. I modified the code a bit, and achieved a run time of less than one minute. Although it'd be nice to think so, this is not because my coding was particularly brilliant; the original author had simply not bothered to look for a better way. They appear to have taken the usual attitude of "if it works, it's good".
Jan 1 '07 #25

NeoPa
Expert Mod 15k+
P: 31,186
Is this a further question you're talking about here?
I hope you have an answer to the first question posted in here (we were just discussing the fix to make it perfect after all). If not, then let us know where you still have a problem.
Jan 1 '07 #26

Expert 5K+
P: 8,435
Regarding my first question, I would like to make a query and say everyone who is younger than 40 for example. I am not up to that yet but I am glad that I got out of the other problem.
This might work...
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE [birthdate] > DateAdd('yyyy',-40,Date());
Or, to use a parameter for the age rather than hard-coding 40...
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE [birthdate] > DateAdd('yyyy',-[Age],Date());
NeoPa, what do you think?
Jan 1 '07 #27

Expert 5K+
P: 8,435
Is this a further question you're talking about here?
I hope you have an answer to the first question posted in here (we were just discussing the fix to make it perfect after all). If not, then let us know where you still have a problem.
The discussion can become confusing I suppose, especially to the less experienced. That's why in that "for those coming in late..." post I tried to summarise the outcome of the discussion thus far.
Jan 1 '07 #28

Expert 5K+
P: 8,435
The discussion can become confusing I suppose, especially to the less experienced. That's why in that "for those coming in late..." post I tried to summarise the outcome of the discussion thus far.
Sorry, wrong thread. That was in the "Help with a Query or SQL select statement..please" one.

Maybe we need a summary of our conclusions here as well.
Jan 1 '07 #29

NeoPa
Expert Mod 15k+
P: 31,186
This might work...
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE [birthdate] > DateAdd('yyyy',-40,Date());
Or, to use a parameter for the age rather than hard-coding 40...
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Tablename
  3. WHERE [birthdate] > DateAdd('yyyy',-[Age],Date());
NeoPa, what do you think?
Your code's fine Killer, but most db apps tend to use a form to get operator input rather than a parameter query. If you're knocking one up for your own use then a parameter's fine.
It also is a good example of how to do it. As good as can be done without further info.
I'm hoping the OP will reply to my question, though, explaining if the questions are happily answered or not.
Jan 1 '07 #30

Post your reply

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