Help using dates as in birthdates

[MMcCarthy]

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

[Killer42]

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]

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"))

[emandel]

Code:
=Int(Format(Dat e(),"yyyy.mmdd" )-Format([Birthday],"yyyy.mmdd" ))

That did it!

Thank you!

[NeoPa]

That did it!

Thank you!

You sound surprised :(.
Seriously - pleased it works for you :).

[emandel]

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!
:)

[Killer42]

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]

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.

[emandel]

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.

[Killer42]

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]

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.

[Killer42]

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?

[Killer42]

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.

[Killer42]

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..plea se" one.

Maybe we need a summary of our conclusions here as well.

[NeoPa]

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.