# in table

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 !!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1

On Wed, 25 Nov 2009 20:07:20 GMT, "instereo911 via AccessMonster.com"
u27979@uwe wrote:

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.
--

John W. Vinson [MVP]