Parsing, Insert, Concantenate

[shexe]

I need to update Birthdates in my table to follow a MMDDYYYY format. The issue lies in some of the dates only having 1 digit. How can I parse the string, insert needed zeros and concantenate back? Any insight is truly appreciated.

[orange]

Why not have them in Date/Time datatype?

How will you determine where the single digit is/represents?

Can you show some sample data?

[shexe]

Format needs to be in Text for an export downstream. Unsure of how to identify where date equals 1 digit. I do have a query that will update the field to the proper format if it isnt compliant (ex. DDMMYYYY,YYYYMMDD, etc) and I'm thinking I can leverage that somehow.

Below is a view of some data from the table. 9111970 should be 09111970 and I can insert leading zeros, however the problem occurs when 231980 should appear as 02031980.

copy_cabcollMember DOB91119701214197023198012291979462009

Does that help clarify?

[shexe]

That table didnt copy over very well. 9111970 and 231980 are examples of when the issue occurs

[pbaldy]

What should 1112011 be converted to?

I agree with orange; the field should be of date/time data type. It's easy to format it as desired for export that way, and no confusion about what the date is.

[shexe]

The date needs to be in Text.

[pbaldy]

It's easy to format a date/time field as text for export. It's hard to store a date in a text field and work with it. We may be able to help you as soon as you tell me how the program will know if 1112011 is supposed to be November 1st or January 11th.

[orange]

How many of these dates are involved?

[orange]

Here's some code that may help get started.
I agree with Paul that you should be using Date datatype and convert to text for export.
I've set up some test data and some logic, but 1111998 is still an issue.

Also if the last 4 characters aren't digits, that's another validation you'd have to do.

Sub parseDate()
Dim i As Integer
Dim BDate(6) As String
BDate(0) = "21031888"
BDate(1) = "2131987"
BDate(2) = "1112001" 'this could be 11 1 or 1 11
BDate(3) = "1961995"
BDate(4) = "12008"
BDate(5) = "3112008"
BDate(6) = "31112008"
Dim x As String
For i = 0 To UBound(BDate)
x = BDate(i)
If Right(x, 4) < "1900" Then
MsgBox "Possible YYYY seems out of whack!!! " & Right(x, 4)
End If
Select Case Len(Trim(x))
Case 8
Debug.Print BDate(i) & " " & Left(x, 2) & " " & Mid(x, 3, 2) & " " & Right(x, 4)
Case 7

' Check if MM is possible
If Mid(x, 3, 2) > "12" Then
Debug.Print BDate(i) & " " & Left(x, 2) & " " & Mid(x, 3, 1) & " " & Right(x, 4)
Else
Debug.Print BDate(i) & " " & Left(x, 1) & " " & Mid(x, 2, 2) & " " & Right(x, 4)
End If
Case 6
Debug.Print BDate(i) & " " & Left(x, 1) & " 0" & Mid(x, 2, 1) & " " & Right(x, 4)
Case Else
MsgBox BDate(i) & " " & "Error BDate is too short"
End Select

Next i

End Sub

[shexe]

Was able to solve for this using a process of 3 queries. I appreciate all the insight and help on this issue.

First
UPDATE copy_cabcoll SET copy_cabcoll.[Member DOB] = "0" & [Member DOB]
WHERE (((Mid([Member DOB],2,1))="/"));

Second
UPDATE copy_cabcoll SET copy_cabcoll.[Member DOB] = Replace([Member DOB],"/","")
WHERE (((copy_cabcoll.[Member DOB]) Is Not Null));

Third
UPDATE copy_cabcoll SET copy_cabcoll.[Member DOB] = Left([Member DOB],2) & "0" & Mid([Member DOB],3,1) & Right([Member DOB],4)
WHERE (((Len([Member DOB]))=7));