Messy data

[Adam]

Hi All,

I have some data from an old legacy system which I want to try and
cleanup.

There is a field called 'CaseTypeFullDesc' and it contains data in the
below format example:

"phone number provided - No callback provided - retailer poor
performance - retail performance"

or it could be:

"retailer poor performance - retailer performance"

Now I want to seperate the data like below:

"Retailer Performance - retailer poor performance - No callback
provided - phone number provided"

and

"Retailer performance - retailer poor performance"

As you can see the data is back-to-front by the "-" and the field
varies in length.

I have looked at the InStr() function however I don't think this would
quite work.

Does anyone know how to split and rejig this data in a query?

Many Thanks
Adam

 

[Allen Browne]

You could write a function that uses Split() to parse the data into an
array, and append the array elements to a related table. You could then
query them as you normally would a related table, or even concatenate the
values back together again.

You will need some understanding of VBA code to achieve that.

 

[Adam]

I have a basic understanding of VB. I cannot find the Split() function
in help?

 

[Pieter Wijnen]

If you're using '97 or earlier it wasn't there.

a simple substitution:

Public Function strSplit(Source As String, Splitter As String) As Variant
Dim strInput As String
Dim iloc As Integer
Dim iCount As Integer
Dim tempArray() As String
strInput = Source
iloc = VBA.InStr(strInput, Splitter)
While iloc > 0
iCount = iCount + 1
ReDim Preserve tempArray(iCount)
tempArray(iCount) = VBA.Left(strInput, iloc - 1)
strInput = VBA.Mid(strInput, iloc + VBA.Len(Splitter))
iloc = VBA.InStr(strInput, Splitter)
Wend
If VBA.Len(strInput) > 0 Then
iCount = iCount + 1
ReDim Preserve tempArray(iCount)
tempArray(iCount) = strInput
End If
strSplit = tempArray
End Function

Pieter

Returns a Variant/String Array

 

[Adam]

I'm using Access 2003.

If you're using '97 or earlier it wasn't there.

a simple substitution:

Public Function strSplit(Source As String, Splitter As String) As Variant
Dim strInput As String
Dim iloc As Integer
Dim iCount As Integer
Dim tempArray() As String
strInput = Source
iloc = VBA.InStr(strInput, Splitter)
While iloc > 0
iCount = iCount + 1
ReDim Preserve tempArray(iCount)
tempArray(iCount) = VBA.Left(strInput, iloc - 1)
strInput = VBA.Mid(strInput, iloc + VBA.Len(Splitter))
iloc = VBA.InStr(strInput, Splitter)
Wend
If VBA.Len(strInput) > 0 Then
iCount = iCount + 1
ReDim Preserve tempArray(iCount)
tempArray(iCount) = strInput
End If
strSplit = tempArray
End Function

Pieter

Returns a Variant/String Array



--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

 

[John Vinson]

I'm using Access 2003.

Open the VBA editor and *then* look for help on Split. There are two
(partially overlapping and less than perfectly designed) Help files;
info about the functions is only available in the VBA help.

I'd really recommend taking a step further: rather than jamming all
these multiple values into one field, consider using Split() to parse
them out into individual records in a new many-side table. They can be
concatenated as needed for display, and will be easier to search.

John W. Vinson[MVP]

 

[Adam]

Yes, I thought of splitting the field but also having the original
field in the one table so that I can report easier later on.

 

[John Vinson]

Yes, I thought of splitting the field but also having the original
field in the one table so that I can report easier later on.

Can't say I'd recommend that! Storing data redundantly is essentially
NEVER a good idea. If you're assuming that you must have the data all
in one table in order to report it, lose that assumption - it's
neither necessary nor practical. Base your Report on a query (which
can assemble the pieces, perhaps with a bit of help from VBA code).

John W. Vinson[MVP]