Complicated sorting problem (1 Viewer)

[raindrop3]

Hello,

I have a little problem. My field contains the following kind of data:

100b1
100b2
100c
100d1
100d2
1a
1a1
1b
1000a
1000a1
1000b

I need to split this field into two fields. All the numbers before the first character must be a field and the remaining characters/numbers must be the second field.

Example:

1000b --> 1000 and b
1a1 --> 1 and a1
1b --> 1 and b
100d2 --> 100 and d2

When the numeric part of the field ends, The new field had to begin.

I don't know if this is possible, but if I can split this field, sorting and searching are a lot easier. It must be possible to search for a range. Eg 100b to 1000c. If the field isn't split, this search isn't possible.

I hope someone can help me with this.

Thanks.

Albert

[This message has been edited by raindrop3 (edited 01-10-2002).]

 

[Ken Grubb]

Public Function SplitData(strInput As String, intSegment As Integer) As String
Dim i, j As Integer
For i = 1 To Len(strInput)
Select Case Asc(Mid$(strInput, i, 1))
Case 48 To 57
' This byte is a number from 0 to 9
Case Else
j = i
Exit For
End Select
Next i

Select Case intSegment
Case 1
SplitData = Left$(strInput, j - 1)
Case Else
SplitData = Right$(strInput, Len(strInput) - j + 1)
End Select
End Function

 

[David R]

Hopefully you've realized that this isn't the way to store this data, if you need to do this sort of manipulation on it. If you can, split it into two fields, or at the least split it later if you're importing this data from a source you can't control.

To get your thought process going, since I'm headed out the door:
You can step through a string with For i = 1 To Len(txt). I would step through your string, find the first letter with the Chr() function, then save the position (i). Exit the For loop, and use Left() and Right() to
parse out the two parts into separate variables.

Hope that helps.
David R

 

[raindrop3]

Ken Grubb, Thanks for your help. I have been trying for more than 2 days, and I alomst had it, but your code helps me out with the last (most important!) things.

David R, I'am not sure if I'am going to store the splitted data. Thanks for your point of view. I also was trying to use left() and right() but I could not make it work. Now it works very fine!

Thanks a lot!

Albert

 

[Ken Grubb]

Echoing what David R said:
It's best to store the two fields separately. It's far easier, and more efficient, to simply concatenate two fields together when needed rather than strip apart one field.