Converting DOB

Topic

New Reply

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?

Reply | Quote

Replies

Try this

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

Reply | Quote

Building on Richard’s answer just a bit:

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.

Reply | Quote

If(([dob])””,mid([dob],5,2) & “/” & right([dob],2) & “/” & left([dob],4),””)

Reply | Quote

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))))

Reply | Quote