Results 1 to 4 of 4

Thread: Converting DOB

  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting DOB

    I have a date of birth that came in to my Access 2003 like this
    19680622
    So I used this formula to convert it to 06/22/1969
    Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4)

    which is what I want but it gives me // when there is no DOB in the DOB field. I try doing a find (//) and replace with nothing and it will not get rid of all the 5000 //.
    Should the formula have something like is null then add nothing?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this

    IIF(IS NULL([DOB], "", Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4))
    Richard

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Building on Richard's answer just a bit:
    Quote Originally Posted by Aheron View Post
    Try this

    IIF(IS NULL([DOB], "", Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4))
    use the IsNull function, and add a closing parenthesis at the indicated location, so that the count of "(" matches the count of ")". Also, add an equals sign if this is the Control Source for a text box on a form:

    =IIF(ISNULL([DOB]), "", Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4))

    Also, make sure that the name of the text box is not DOB, otherwise, you will get a circular reference error. I recommend naming the control txtDOB. You can use the same expression in a query, but you will need to alias the field name different from DOB, or else you will have the same circular reference error. Something like this, as the Field expression. Note that you omit the equals sign in a query:

    Field: CalcDOB: IIf(IsNull([DOB]),"",Mid([DOB],5,2) & "/" & Right([DOB],2) & "/" & Left([DOB],4))

    Either way, calculated on-the-fly in a query or setting the Control Source on a form to the expression, you will end up with a read-only result for the calculated DOB value. Instead, you might want to consider running an UPDATE query, to update a new field with data type Date/Time to the actual date, instead of leaving it as a string. This will allow you to easily calculate a person's age, based on the computer's system time and the DOB value.
    Last edited by tgw7078; 2013-10-19 at 03:32.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  4. #4
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you'd like to convert the date to a date/time value (or at least a "Variant of type date"), you can use the DateSerial function (building off of Aheron & Tom's code):

    CalcDOBDate: IIf(IsNull([DOB]),"",DateSerial(CInt(Left([DOB],4)),CInt(Mid([DOB],5,2)),CInt(Right([DOB],2))))

Posting Permissions

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