Need Help with separating a Semicolon delimited data in a Memo Field

[WillyBilly]

Hi,
I am very new to this,
I have a Memo field that contains data between semicolons and I was
hoping to separate this items and put them on on single columns with a
record.

Any tips on a VBA procedure that would help me ?
I am using the following to extract the first string :

Public Function SeparateFirstfield(FieldIn) As String
Dim intX As Integer
intX = InStr(1, [FieldIn], ";")
SeparateFirstfield = Left(FieldIn, intX - 1)
End Function

(thanks to an earlier posting)

and the following to get to the second :
Public Function SeparateTitle1(FieldIn) As String
Dim intX1 As Integer
intX1 = InStr(32, [FieldIn], ";")
SeparateTitle1 = Mid(FieldIn, intX1 + 1, ) *** and I endeup getting
everything else in the field.

any help would be appreciated..
Thanks

 

[Michael J. Strickland]

Hi,
I am very new to this,
I have a Memo field that contains data between semicolons and I was
hoping to separate this items and put them on on single columns with a
record.

Any tips on a VBA procedure that would help me ?
I am using the following to extract the first string :

Public Function SeparateFirstfield(FieldIn) As String
Dim intX As Integer
intX = InStr(1, [FieldIn], ";")
SeparateFirstfield = Left(FieldIn, intX - 1)
End Function

(thanks to an earlier posting)

and the following to get to the second :
Public Function SeparateTitle1(FieldIn) As String
Dim intX1 As Integer
intX1 = InStr(32, [FieldIn], ";")
SeparateTitle1 = Mid(FieldIn, intX1 + 1, ) *** and I endeup getting
everything else in the field.

any help would be appreciated..
Thanks

You can use the Split function to parse a string's (or field's)
components into an array. Then assign each element of this array to
the desired fields in your table.

For example, assuming your memo field is FieldIn:

Dim strArray() As String
strArray = Split(FieldIn, ";")


--

 

[WillyBilly]

Hi,
I am very new to this,
I have a Memo field that contains data between semicolons and I was
hoping to separate this items and put them on on single columns with a
record.

Any tips on a VBA procedure that would help me ?
I am using the following to extract the first string :

Public Function SeparateFirstfield(FieldIn) As String
Dim intX As Integer
intX = InStr(1, [FieldIn], ";")
SeparateFirstfield = Left(FieldIn, intX - 1)
End Function

(thanks to an earlier posting)

and the following to get to the second :
Public Function SeparateTitle1(FieldIn) As String
Dim intX1 As Integer
intX1 = InStr(32, [FieldIn], ";")
SeparateTitle1 = Mid(FieldIn, intX1 + 1, ) *** and I endeup getting
everything else in the field.

any help would be appreciated..
Thanks

You can use the Split function to parse a string's (or field's)
components into an array. Then assign each element of this array to
the desired fields in your table.

For example, assuming your memo field is FieldIn:

Dim strArray() As String
strArray = Split(FieldIn, ";")


--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
---------------------------------------------------------------

Thank you Michael,
I wil ltry try it . ANy tips on how to extract it each item ? I'd
appreciate it.

 

[Douglas J. Steele]

Thank you Michael,
I wil ltry try it . ANy tips on how to extract it each item ? I'd
appreciate it.

Read the help file for how the Split function works.

If your text contains

String of text 1; String of text 2; String of text 3

then after using the Split function, strArray(0) will contain "String of
text 1", strArray(1) will contain "String of text 2" and strArray(2) will
contain "String of text 3"

 

[lkk]

Hi,
I am very new to this,
I have a Memo field that contains data between semicolons and I was
hoping to separate this items and put them on on single columns with a
record.

Any tips on a VBA procedure that would help me ?
I am using the following to extract the first string :

Public Function SeparateFirstfield(FieldIn) As String
Dim intX As Integer
intX = InStr(1, [FieldIn], ";")
SeparateFirstfield = Left(FieldIn, intX - 1)
End Function

(thanks to an earlier posting)

and the following to get to the second :
Public Function SeparateTitle1(FieldIn) As String
Dim intX1 As Integer
intX1 = InStr(32, [FieldIn], ";")
SeparateTitle1 = Mid(FieldIn, intX1 + 1, ) *** and I endeup getting
everything else in the field.

any help would be appreciated..
Thanks

You can use the Split function to parse a string's (or field's)
components into an array. Then assign each element of this array to
the desired fields in your table.

For example, assuming your memo field is FieldIn:

Dim strArray() As String
strArray = Split(FieldIn, ";")


--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
---------------------------------------------------------------

Thank you Michael,
I wil ltry try it . ANy tips on how to extract it each item ? I'd
appreciate it.