Date function problem

[GingerScouser]

Hi,

I'm currently experiencing some problems with my Access 2000 Database. I'm designing a database for a Youth Football Team you see, but have become a little stuck on something.

In a table I have, I've created a field called DOB. What I want to do, is be able to enter a certain age, but have it be between specific ones.

E.g. If the player is in the U-13's team, and I enter his DOB as 14/12/1987, then an error message will pop up, as that would mean the person would be too old, just the same as it will if I enter something such as 01/04/2000, meaning the player would be too young.

I've tried numerous things to accomplish this, but am constantly failing. I have five tables which I want to use this in, and the one I'm currently testing it in is the Under 12's Team table.

I have tried using this validation rule:

=Now()-[DOB]<12 And Now()-[DOB]>11

Meaning that the player can be no less than the age of 11 but no more than the age of 12. However, it doesn't work whatsoever, because when I enter an erroneous piece of data, it allows it, even though all of my validation is correct.


I'd much appreciate any help that can be provided on this. (I'll also try my best to help others )

Thanks,
Paul

 

[namliam]

I think this will need a user defined function as the datediff (also a handy function, look it up on the forum or the access help) between DOB and now() (or probably better, Date()) depends upon the team selection.

However you might need to check not on date() but a set date. In Basketball here in the NL we say a person cannot be 13 years of age at 01-jan of that year. So if a person is born on 02 jan 1991 he is elligable.... born 31-12 1990, not...

Keep that in mind and check the datediff function, Experiment with that and try to create your funtion. If you have troubles, ask away and there will be plenty ppl happy to help....

Regards

 

[GingerScouser]

Thanks for the tips, I'll give the whole beginning of the year thing a try, that sounds as if it could work.

I did actually try the DateDiff function on it, but it didn't work. My ICT teacher assisted me in that, so I think I tested that to the max.

I'll give it all a shot anyway, thanks again.

 

[Mile-O]

There's AGE functions in the archives that you can use if you have a search for them - these work off of the DateOfBirth.

 

[GingerScouser]

Got it at last! (I get by with a lil' help from my friends )

I used this formula...

>#30/07/1992# And <#01/08/1993#


Meaning that the player must be born AFTER 1st August 1992 but BEFORE July 30th 1993. Took me that long to figure out something so simple. Ah well, can press on with it now!

 

[Mile-O]

>#30/07/1992# And <#01/08/1993#

Between #31/07/1992# And #01/08/1993#


The only thing I find wrong with it is that it is hard-coded in to the database and you will need to come back to the database design in one year to change the criteria.

You can use date functions to calculate this for any year.


This is the criteria you probably want:

Between DateAdd("yyyy",-12,DateSerial(Year(Date()),7,31)) And DateAdd("yyyy",-11,DateSerial(Year(Date()),8,1))

 

[ColinEssex]

I used this formula...

>#30/07/1992# And <#01/08/1993#

Meaning that the player must be born AFTER 1st August 1992 but BEFORE July 30th 1993. Took me that long to figure out something so simple. Ah well, can press on with it now!

If you want it after the 1st Aug 1992 and before 30th July 1993, you need

>#01/08/1992# And <#30/07/1993#

with your formula you would get people with a DOB of
31/07/1992
01/08/1992
which you said you don't need
and
31/07/1993
which you said you don't need

Col

 

[GingerScouser]

Oops, my mistake. What I meant to say was AFTER July 30th and BEFORE August 1st.

 

[raskew]

Ginger -

See if this might be helpful--

look here

Bob

 

[GingerScouser]

I've come to the conclusion that, at the end of every season, I'll just change the name of the tables and then edit the relationships. It's the simplest way I can see of doing it right now. I may go back to it at the end, if I have time.

Thanks for all your help, I really appreciate it

 

[Mile-O]

You are not entering dates directly into the table are you?