Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Pennsylvania, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates to Character (Access 2000 - )

    I am trying to update an Access 2K db that I did not create - The user would like a DOB [date of birth] to reflect a category for other processing - Youth / Teen /Adult - Is there any way that a DOB can generate a character in a column that was previously MANUALLY entered? The dates are there and I do not want to have the user reload the entire dataset for 2000+ names. Any help would be GREATLY appreciated!

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates to Character (Access 2000 - )

    How about:

    1) Add the Category column (if it doesn't already exist)

    2) Create an update query that updates this field with Category in the Field row and a nested iif statement of the form:

    IIf("DOB"<=#01/01/2001#,"A",IIf("DOB"<=#01/01/2002#,"C","D"))

    in the Update To row.

    I avoid using short date formats in queries as our British way of aranging dates as dd/mm/yy confuses the query engine.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Dates to Character (Access 2000 - )

    <P ID="edit" class=small>(Edited by MarkD on 16-Jan-04 07:23. Entered correction in sample code.)</P>You could use some user-defined functions for this purpose. Example:

    Public Function GetAge(ByRef StartDate As Date, ByRef EndDate As Date) As Integer

    ' Get age in years
    Dim intAge As Integer

    intAge = DateDiff("yyyy", StartDate, EndDate)
    If EndDate < DateSerial(Year(EndDate), Month(StartDate), Day(StartDate)) Then
    intAge = intAge - 1
    End If
    GetAge = intAge

    End Function

    Public Function GetAgeCat(ByRef DateOfBirth As Date) As String

    Dim intAge As Integer
    intAge = GetAge(DateOfBirth, Date)

    ' Get Age category based on Date of Birth:

    Select Case intAge
    Case Is < 4
    GetAgeCat = "Toddler"
    Case Is < 13
    GetAgeCat = "Child"
    Case Is < 21
    GetAgeCat = "Teenager"
    Case Is < 65
    GetAgeCat = "Adult"
    Case Else
    GetAgeCat = "Senior"
    End Select

    End Function

    Example of use:

    ? GetAgeCat("1/15/2002")
    Toddler
    ? GetAgeCat("1/15/1999")
    Child
    ? GetAgeCat("1/15/1990")
    Teenager
    ? GetAgeCat("1/15/1976")
    Adult
    ? GetAgeCat("1/15/1936")
    Senior

    Of course the Age Categories defined in Select Case statement can be redefined to meet your specific requirements - this is just an example. Since a person's age is dynamic, not static, I would not store the age category in a table, but use the GetAgeCat function where needed to display the applicable category based on the person's date of birth.

    HTH

Posting Permissions

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