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?
29 1697
Anyone out there to help?
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. -
-
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])
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)
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?
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
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
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?
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
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?
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"))
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?
--------------------------------------------------------------------------------
Just this one ... - =DateDiff ("yyyy", Format([birthdate], "Short Date"), Format(Now(), "Short Date"))
Just a thought
Have you checked that your computers system date is correct?
Mary
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
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
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.)
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?
- SELECT *
-
FROM Tablename
-
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?
- =Int(Format(Date(),"yyyy.mmdd")-Format([Birthday],"yyyy.mmdd"))
Code:
=Int(Format(Date(),"yyyy.mmdd")-Format([Birthday],"yyyy.mmdd"))
That did it!
Thank you!
NeoPa 32,556
Expert Mod 16PB
That did it!
Thank you!
You sound surprised :(.
Seriously - pleased it works for you :).
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!
:)
Brilliant Killer.
Thank you. :) - SELECT *
-
FROM Tablename
-
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... -
SELECT *
-
FROM Tablename
-
WHERE [birthdate] < DateAdd('yyyy',-35,Date());
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.
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.
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".
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.
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... - SELECT *
-
FROM Tablename
-
WHERE [birthdate] > DateAdd('yyyy',-40,Date());
Or, to use a parameter for the age rather than hard-coding 40... - SELECT *
-
FROM Tablename
-
WHERE [birthdate] > DateAdd('yyyy',-[Age],Date());
NeoPa, what do you think?
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.
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.
NeoPa 32,556
Expert Mod 16PB
This might work... - SELECT *
-
FROM Tablename
-
WHERE [birthdate] > DateAdd('yyyy',-40,Date());
Or, to use a parameter for the age rather than hard-coding 40... - SELECT *
-
FROM Tablename
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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......
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |