adding a comma into imported text

  • Thread starter Thread starter Marie
  • Start date Start date
M

Marie

I import a text file into my Access database and it is
several dates appearing as follows:
2003/032003/042003/05
I would like to insert a comma and a space between each
month so it will appear as: 2003/03, 2003/04, 2003/05
Any ideas?
 
I import a text file into my Access database and it is
several dates appearing as follows:
2003/032003/042003/05
I would like to insert a comma and a space between each
month so it will appear as: 2003/03, 2003/04, 2003/05
Any ideas?
Click to expand...

Are there always just three dates, or an arbitrary number? Are they
COMPLETELY consistant (i.e. might there be a record with 2003/5
instead of 2003/05)? I would suggest that (at some point) you'll want
to have two tables in a one to many relationship, with one record per
date, rather than having multiple dates in one field (with or without
commas).

You'll probably need a little VBA function to parse this out if (as I
suspect) there are variable numbers of dates. If there are always
three and only three, try

Left([textfield], 7) & ", " & Mid([textfield], 8, 7) & "," &
Mid([textfield], 15, 7)


John W. Vinson[MVP]
 
Thank you for responding. The months are always
different. There may only be one month or as many as 24
months. The format will always be consistent...2003/05...
I tried yyyy/mm: Right([period],2)+"/"+(Left([period],4)
+",") but it only shows one month when there should be
several others.
Any ideas?

-----Original Message-----
I import a text file into my Access database and it is
several dates appearing as follows:
2003/032003/042003/05
I would like to insert a comma and a space between each
month so it will appear as: 2003/03, 2003/04, 2003/05
Any ideas?
Click to expand...

Are there always just three dates, or an arbitrary number? Are they
COMPLETELY consistant (i.e. might there be a record with 2003/5
instead of 2003/05)? I would suggest that (at some point) you'll want
to have two tables in a one to many relationship, with one record per
date, rather than having multiple dates in one field (with or without
commas).

You'll probably need a little VBA function to parse this out if (as I
suspect) there are variable numbers of dates. If there are always
three and only three, try

Left([textfield], 7) & ", " & Mid([textfield], 8, 7) & "," &
Mid([textfield], 15, 7)


John W. Vinson[MVP]
.
Click to expand...
 
Thank you for responding. The months are always
different. There may only be one month or as many as 24
months. The format will always be consistent...2003/05...
I tried yyyy/mm: Right([period],2)+"/"+(Left([period],4)
+",") but it only shows one month when there should be
several others.
Any ideas?
Click to expand...

Ok... you'll need some VBA code to parse this. (Is there ANY way you
can get the data in a more rational format than 2003/032003/042003/05
!?)

Air code, untested:

Public Function AddCommas(strIn As String) As String
' Parse 2003/032003/042003/05 dates into
' 2003/03, 2003/04, 2003/05
Dim iLen As Integer
Dim iPos As Integer
AddCommas = ""
iLen = Len(strIn)
If iLen = 0 Then Exit Function 'nothing to do
If iLen MOD 7 <> 0 Then
MsgBox "Invalid number of characters:" & vbCrLf & strIn, _
vbOKOnly
Else
For iPos = 1 to iLen - 6
AddCommas = AddCommas & Mid(strIn,iPos,7) & ", "
Next iPos
End If
End Sub


John W. Vinson[MVP]
 
Back
Top