# in table

[instereo911 via AccessMonster.com]

Hi,

I have a table w/ multiple values on one column seperated out by a ";#" sign.


Example:
State Number
;#Central;#COVA;# 1


Ideally, what i want to do is the following

State Number
Central 1
COVA 1

So it breaks it out to two different rows for each of the ;#

Is this possible? I hope so because I don't want to redo everything..


Thanks again everyone !!!

 

[John W. Vinson]

Hi,

I have a table w/ multiple values on one column seperated out by a ";#" sign.


Example:
State Number
;#Central;#COVA;# 1


Ideally, what i want to do is the following

State Number
Central 1
COVA 1

So it breaks it out to two different rows for each of the ;#

Is this possible? I hope so because I don't want to redo everything..


Thanks again everyone !!!

Possible but tedious. It will probably be easiest if you write a little VBA
function to help parse it out:

Public Function GetState(strIn As String, iPos As Integer, _
Optional strDelim As String = ";#") As String
Dim States() As String
States = Split(strIn, strDelim)
If UBound(States) >= iPos Then
GetState = States(iPos)
Else
GetState = ""
End If
End Function

You can then run an Append query using GetState([fieldname], 1) to pull out
the first value, 2 the next, etc.; use a criterion of <> "" on the calculated
field to return only the non-empty states. Or, you could use VBA to loop
through the string and append records to an open recordset.