473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help using dates as in birthdates

emandel
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
29 1697
emandel
65
Anyone out there to help?
Jan 1 '07 #2
MMcCarthy
14,534 Expert Mod 8TB
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
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
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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
Just this one ...

Expand|Select|Wrap|Line Numbers
  1. =DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))
Jan 1 '07 #13
MMcCarthy
14,534 Expert Mod 8TB
Just a thought

Have you checked that your computers system date is correct?

Mary
Jan 1 '07 #14
emandel
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
14,534 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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
65
Code:
=Int(Format(Date(),"yyyy.mmdd")-Format([Birthday],"yyyy.mmdd"))
That did it!

Thank you!
Jan 1 '07 #19
NeoPa
32,556 Expert Mod 16PB
That did it!

Thank you!
You sound surprised :(.
Seriously - pleased it works for you :).
Jan 1 '07 #20
emandel
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

5
by: ruud habets | last post by:
while making an encyclopdie on my hometown (dutch, http://www.kgv.nl/index.php?id=610) i am trying to use dates prior to 1970. birthdates in, let's say, 1615. i'm getting errors while working...
2
by: Ian DeRock | last post by:
I'm fairly new to XML. As I can see it, XML is a way to organize data like in a table in a RDB, or the RDB itself. I have used XML in application data, but did not design the actual XML. I...
9
by: Marcus Sheen [UK] | last post by:
Have done searches for similar questions, but cannot find anything. Nor can I find any resources via Google (Javascript Source etc). Thinking about implementing a topical "On This Day" feature on...
8
by: Lyn | last post by:
I am trying to get my head around the concept of default, special or empty values that appear in Access VBA, depending on data type. The Access Help is not much (help), and the manual that I have...
0
by: Brian Henry | last post by:
Ok I've never implemented a snap location before so I dont really know what im doing wrong here... anyways, I am making a custom slider control that takes dates as its values instead of integers......
4
by: Working_Girl | last post by:
Hi, I have a database with insurance clients and their dependents (spouses and children). We had a problem in the past with the twins and some of them have been entered with one month...
2
by: mario | last post by:
Hi, I have verry big problem. I must write function using dates. This is a problem: I have some dates for example: 2006-01-01, 2006-02-05, 2006-10-15 etc. then some times: 20sec, 50sec, 45sec...
2
by: Bill | last post by:
I have a 200 record database that includes a date/time field, AnnivDate, for a wedding anniversary. AnnivDate has nulls and some incorrect year data. I have been creating the Access database...
2
by: Jim Carlock | last post by:
(1) Does PHP provide any way to handle dates prior to 1980? I know there's problems with Microsoft Windows NT and all Windows NT operating systems will allow a date prior to 1980 to be placed...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.