Auto input age categories base on DOB 1

[frodeb]

I am trying to make a fild in a query to display his age categori base on his DOB. Rules:

Age Categories

Sub-Junior : from the day he reaches 14 years and throughout the full calendar year in which he reaches 18 years.

Junior : from 1 January in the calendar year he reaches 19 years and throughout the full calendar year in which he reaches 23 years.

Senior : from 1 January in the calendar year he reaches 24 years and throughout the full calendar year in which he reaches 39 years.

Master 1 : from 1 January in the calendar year he reaches 40 years and throughout the full calendar year in which he reaches 49 years.

Master 2: from 1 January in the calendar year he reaches 50 years and throughout the full calendar year in which he reaches 59 years.

Master 3: from 1 January in the calendar year he reaches 60 years and upwards

eks: query name= test
[DOB]: 23.07.1978 (input data from use) (DD.MM.YY)
[AGE CAT] Senior (auto entered)

Can someone pls help me with this problem.

 

[SteveR77]

First you will need the age calculation -

Int(Datediff("d", [DOB], now())/365.25)

This will handle the issue of the full calendar year because 29.9 will return 29 as the current age.

Assuming we use the calculation above to create a new field in the query called CurrentAge

[The field would be expressed as follows - CurrentAge:Int(Datediff("d", #10/04/1966#, now())/365.25) ]

In the AGE CAT field the following IIF expression would provide you with the category assignment:


IIF([CurrentAge] Between 14 and 18, "Sub-Junior",
IIF([CurrentAge] Between 19 and 23, "Junior",
IIF([CurrentAge] Between 24 and 39, "Senior",
IIF([CurrentAge] Between 40 and 49, "Master1",
IIF([CurrentAge] Between 50 and 59, "Master2","Master3&quot))))

HTH,

Steve

 

[THWatson]

Steve
I'm curious. Once the query establishes the current age, would there be any advantage in using the Switch function to get the AGE CAT?

Tom

 

[r937]

steve, your division by 365.25 works fine except for two situations:

1. when DOB and now() are both near the dec31/jan1 border -- i would like to see a range of test data verify that you can actually snag the correct year (use several days on either side, and do it for a 16-year-old as well as an 80-year-old)

2. Sub-Junior operates only from the actual 14th birthday, so the age calculation has to be exact for that day

rudy

http://r937.com/

SQL Consulting

 

[SteveR77]

Tom,

Switch or a select could be used as far as an advantage from a performance point of view I have not run any test to see which coding construct would be the most advantageous.

Steve

 

[SteveR77]

Rudy,


Thanks for the catch on the formula. Going with the standard 365 day year will fix that.

Int(Datediff("d", [DOB], now())/365)


Steve

 

[r937]

no, i don't believe it will, it will not accurately get the 14th birthday

here's a much more accurate age formula:

CurrentAge: year(date()) - year(DOB)
- iif(month(date())>month(DOB),0,
iif(month(date())<month(DOB),1,
iif(day(date())<day(DOB),1,0)))


rudy

http://r937.com/

SQL Consulting

 

[MichaelRed]

Public Function basAgeCat(DOB As Date, Optional AsOf As Variant) As String

    Dim Idx As Integer
    Dim MyAsOf As Date
    Dim Cat(6) As String
    Dim CatYr(6) As Integer
    Dim YrAge(6, 1) As Date

    Cat(0) = ""
    Cat(1) = "Sub Junior"
    Cat(2) = "Junior"
    Cat(3) = "Senior"
    Cat(4) = "Master 1"
    Cat(5) = "Master 2"
    Cat(6) = "Master 3"

    CatYr(0) = 0
    CatYr(1) = 0
    CatYr(2) = 5
    CatYr(3) = 15
    CatYr(4) = 10
    CatYr(5) = 10
    CatYr(6) = 140

    If (Not IsMissing(AsOf)) Then
        If (IsDate(AsOf)) Then
            MyAsOf = AsOf
        End If
     Else
        MyAsOf = Date
    End If

    'Calculate the various date ranges for the categories
    YrAge(0, 0) = 0                                             'Below range, No Category
    YrAge(0, 1) = DOB                                           'Actual Date of Birth

    YrAge(1, 0) = YrAge(0, 1)                                   'Sub Junior
    YrAge(1, 1) = DateSerial(Year(DOB) + 18, 12, 31)            'DOB-14th to 12/31/18th

    Idx = 2
    While Idx <= UBound(YrAge)
        YrAge(Idx, 0) = DateAdd(&quot;d&quot;, 1, YrAge(Idx - 1, 1))               '1/1/19th to 12/31/24th
        YrAge(Idx, 1) = DateAdd(&quot;YYYY&quot;, CatYr(Idx), YrAge(Idx - 1, 1))               '1/1/19th to 12/31/24th
        Idx = Idx + 1
    Wend

    Idx = 0
    While Idx <= UBound(YrAge)

        If (basBtwnDt(YrAge(Idx, 0), MyAsOf, YrAge(Idx, 1)) = True) Then
            basAgeCat = Cat(Idx)
            Exit Function
        End If

        Idx = Idx + 1
    Wend

End Function
Public Function basBtwnDt(dtStrt As Date, dtTest As Date, dtEnd As Date) As Boolean

    If ((dtStrt <= dtTest) = (dtTest <= dtEnd)) Then
        basBtwnDt = True
    End If

End Function
[code]





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

frodeb, are you still out there?

Age calculations are a popular topic in Tek-Tips.
And Michael ALWAYS provides excellent assistance!

If you're already invested in the previously-suggested solution, this (slight adjustment to SteveR77's formula)
is straight-forward and allows for leap years.

Int(((Date()-[DOB])+.5)/365.2425)

B4 you ask, + .5 is to show someone is a year older ON their birthday instead of the following day.

HTH,
Bob

 

[frodeb]

Thx for all the replays, I realy appreciate it, but none of the seems to work according to the rules (except MichaelRed i don`t know where to put the code in the query (in a form i know, but i need it in a query for later use)

Let say that one is born 20.04.1980 (dd.mm.YYYY)

1st rule: He can not participate befor 20.04.1994 (14) (If the competition is befor this date the fild will display &quot;to young&quot;.

If the competition is between 20.04.1994 and 31.12.1998 the age categori fild will display &quot;sub Junior&quot;. And if it is on the 01.01.1999 (he is still 17) the fild will display &quot;Junior&quot; and so on. (01.01.1999 to 31.12.2003 = Junior and 01.01.2004 to 31.12.2019 = Senior) and so on. (see top)

I am out of ide so if someone have the answer i would realy appreciate it. I need it for my next step, when the score sheet (report) is going to sort the participants in age categori to see the winner in each categories.

 

[frodeb]

Sorry i mean 18 (in the date 01.01.1999)

 

[MichaelRed]

Actually, you didn't specify a &quot;Category&quot; for hte under 14 in your original post, so my code is incomplete, and would require at least a minimal modification to return the category as &quot;to young&quot;. Otherwise, you would place the code in GEMERAL module and address it in a query as:

AgeCat: basAgeCat([DOB])

To return the age category as of the date the query is executed.


Or:

AgeCat: basAgeCat([DOB], SomeSelectedDate)
To return the age category as of the date desired.


While I do not mean to belittle your efforts, If you need to ask this question (&quot; ... don`t know where to put the code ... &quot then you will quite probably have quite a bit of trouble in generating much in the way of a 'professional' application and really should get some mre immediate help than is generally available in these fora. In general, the membership here is expected to have a general working knowledge of the applications and only want / need help in some specific detail of a topic. I would suggest that you visit your local bookstore and purchase a text / tutorial on the application.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[frodeb]

MichaelRed your code worked excellent (I notes that &quot;Master I&quot; change 1 year to early, but i change the CatYr(3) = 15 to CatYr(3) = 16 and it worked perfect)Thank you for helping me. BTW could you make the code to also display &quot;to young&quot; I would realy appreciate it.

I am sorry if my knowledge is not &quot;expert&quot;, but i am trying to get there. I know may way around MS Access, but i have only used macro and expression. My purpose was only to get help to solve the problem. Since I have not used code with query and could not confirm that you code worked. I was only trying to explain way i have not testet it, but i thank you for takeing the time to show me how to use code in query.

 

[MichaelRed]

Change Cat(0) = &quot;&quot;

[tab][tab][tab]To

Cat(0) = &quot;to young&quot;


Again, not to demean your ability, but the use of &quot;macro&quot;s is, perhaps, the first symptom of being an amature. If you seriously intend to persue ANY &quot;programming&quot;, make the visit to 'Ye Olde Bookshoppe&quot;. VB / VBA is a reasonable language to start with in learning programming.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places