Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Unhappy Answered: Replace calculated age with text on form.

    Hello all and a belated Happy New Year.

    I have a seemingly simple problem that I can't seem to sort out and am looking for some solutions.

    In my DB there is a Date Of Birth Field (Date/Time)

    I am calculating the persons age using
    Code:
    = Year(Now()) - Year([Date Of Birth]) + (DateSerial(Year(Now()), Month([Date Of Birth]), Day([Date Of Birth])) > Now())
    and displaying the result on a form as the control source for a TextBox called Age.

    This works fine.

    What I now want to do is have one of 3 values displayed in another TextBox on the form called AgeGroup.

    The AgeGroup will be either "Infant", "Junior" or "Senior" based on the following criteria;

    Infant <11
    Junior >= 11 and <30
    Senior >=30

    My searches over the last couple of days have given me tantalizing glimpses of how to achieve this but I can't seem to get it right.

    Can someone kindly point me in the right direction please.

  2. Best Answer
    Posted by healdem

    "Use an IIF statement
    If ([boolean expression], [value if true], [value if false])
    You can 'compound' IIF statements, replace the [value if false] with further IIF statements.
    The boolean expression means any statement which results in a boolean value of true or false
    The value segments can be calculated or literals

    Eg
    IIf (A < 11,"Junior", IIF ( A >= 30, "Senior", "Junior"))

    You may need to wrap some form of error handler if you dont know the persons date of birth, or add that error handle to the compound IIF."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Use an IIF statement
    If ([boolean expression], [value if true], [value if false])
    You can 'compound' IIF statements, replace the [value if false] with further IIF statements.
    The boolean expression means any statement which results in a boolean value of true or false
    The value segments can be calculated or literals

    Eg
    IIf (A < 11,"Junior", IIF ( A >= 30, "Senior", "Junior"))

    You may need to wrap some form of error handler if you dont know the persons date of birth, or add that error handle to the compound IIF.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Healdem

    Thanks for the reply.

    So if I understand you correctly I could use

    Code:
    Dim AgeGroupValue as Integer
    AgeGroupValue = Year(Now()) - Year([Date Of Birth]) + (DateSerial(Year(Now()), Month([Date Of Birth]), Day([Date Of Birth])) > Now())
    IIF (AgeGroupvalue<11,"Infant", IIF(AgeGroupValue >=30,"Senior","Junior"))
    Now if I understand the code correctly it reads

    If AgeGroupValue <11 then display "Infant"
    If AgeGroupValue >= 30 then Display "Senior"
    otherwise display "Junior"
    Is this correct?
    Do i place this in the Control Source of the text field?

    My apologies but I haven't had to deal much with dates in the past.

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    How you use that is up to you
    You could use a function, then call that function in a query, form or report. Any time you could use this sort of process in more than one form or report is a good candidate for a function.
    If the function is to be called from different forms or reports the function goes into a code module (ie as vba code that usnt associated with a specific form or report. If its only to be used in multiple places in the same report or form in can go in that report or forms code, but id suggest always put functions in a code module. If its to be used in a query it must go in a code module
    Another advantage of using a function us that you can force datatypes to be if the correct type by defining the datatypes of the incoming parameter. You can build in error checking. Say the incoming parameter is a blank date or is a value out of range.. because yoy use a write once use many gimes approach if the business requirements change then with a carefully crafted function you change that function and all forms, reports and queries usibg that function automatically benefit from that function. Ferinstance say there is a wish to add a 4th membership lable.. if over 75 then "Pensioner".

    If you do use a function yhen there us no need to yse tge iif construct use a standard if or a select case statement
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here is a Age function I wrote

    Code:
    Function Age(Birth_Date,End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    If IsNull(Birth_Date) or Birth_Date ="" Then
        Age=0.0 
    else
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 then Years = "" 
        Age =  Years & "." & Months - Temp
    End if
    End Function
    to used in a Query

    Age:Age([feildnamedate],date())

    now it return year.months

    so

    MyAge = age(#01/01/65#,date()) = 51.0

    MyAge = age(#01/01/65#,#12/01/15#) = 50.11

    MyAge = age(#01/01/65#,#02/01/16#) = 51.01
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #6
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hello Healdem
    Thanks for the advice. Your solution worked perfectly. Apologies for the late response, I've been on holidays.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •