Trouble with Update Query - Convert numbers to Date Format

[MI_XD]

Having trouble with an Update Query. Using two text format fields, trying to change a DOB field to NewDOB field formatted using xx/xx/xx.
DOB field examples:
92115


111015
81714
102315

Using this formula I get bad results, as they do not seem to recognize all the numbers.

IIf(("Len[DOB]=5"),Left([DOB],1),Left([DOB],2)) & "/" & IIf("Len[DOB]=5",Mid([DOB],2,2),Mid([DOB],3,2)) & "/" & Right([DOB],2)

Examples of results I am getting:


Any help on this formula or problem would be appreciated.
Thanks,

Bob
Win 10 Pro
Access 2016

[CJ_London]

this

("Len[DOB]=5")

should be

Len([DOB])=5

i.e.

IIf((Len([DOB])=5,......

[orange]

Do you realize that MSAccess provides a Date/Time datatype to be used with dates?
And a series of related functions to manipulate dates.

Here is info about Iif
Good luck.

[MI_XD]

this

("Len[DOB]=5")

should be

Len([DOB])=5

i.e.

IIf((Len([DOB])=5,......

I thought it was strange, but the auto formatting changed it to have the "" at one point. Guess when I was making changes. I had tried all variations, but it never seemed to work.

Just made the corrections so it is formatted as Len([DOB])=5, and it WORKED!

Thanks, Ajax!

[MI_XD]

Do you realize that MSAccess provides a Date/Time datatype to be used with dates?
And a series of related functions to manipulate dates.

Here is info about Iif
Good luck.

Orange,
Yes I do, but I needed to get the date from the text string form that the data was imported as, into a date format with the "/" marks.

Thanks!

[cyanidem]

Just wondering... What if DOB is "11109"? Is it 1/11/09 or 11/1/09?

[ssanfu]

Also could use

Left([DOB], IIf(Len([DOB]) = 5, 1, 2)) & "/" & Mid([DOB], 2, IIf(Len([DOB]) = 5, 2, 3)) & "/" & Right([DOB], 2)

A little shorter, but easier to read?? (maybe??)