String Manipulation

[privateagentx]

Ok I need some help. I have a database that stores a list of drawings. The data can be a single drawings, for example "e45" or a series of drawings, for example "e1 thru e45". I need code that can take the text for a series of drawings and expand it and load them in a listbox. For example, take the text "e1 thru e5" and expand it to read "e1" "e2" "e3" "e4" "e5" as single drawings on separate lines in a list box. Thanks for your help in advance.

[Kdev]

Dim sDrawings as string
Dim nPos as Integer
Dim lBegin as Long
Dim lEnd as Long
Dim i as Long


sDrawings = "e1 thru e5"
nPos = InStr(sDrawings, "thru")



If nPos <> 0 then
lBegin = mid(sDrawings, 2, nPos - 3)
lEnd = mid(sDrawings, nPos + 6)
else
lBegin = mid(sDrawings, 2)
lEnd = lBegin
End If



for i = lBegin to lEnd
List1.AddItem("e" & i)
next i


This obviously assumes all drawings are named 'eX' where X is any valid numeric character(s).

This does no error checking but I hope you get the basic idea of how the algorithm works.

-K

[dfwade]

There a bunch of answers to this one and most relate to how you are storing your information. For the example you give, I assume that you want to store all of the drawing information in a single field. In order to do this you want to have a set delimiter between the drawing type, like this e1|e2|e3|e4.
you can use the split and join functions to read the data into and out of this format into an array. Once you have the array you can loop through it. For instance

private Sub Form_Load()
'Add a list box to your form
'Setup up a string

Dim lngLoop as Long 'Loop Counter
Dim lngCnt as Long ' Number to times to loop
Dim str as string ' Hold the string that would normally be read in from the database

Dim test() as string 'Array of strings

str = "e1|e2|e3|e4|"

test = Split(str, "|") 'Split the string into an Array
lngCnt = UBound(test) - 1 'set the Upper bounds of the Array
for lngLoop = 0 to lngCnt 'Start Looping
List1.AddItem test(lngLoop)
next

End Sub




You can use the join command just as easily to send data in the right format to the database

[privateagentx]

thanks for your help. the "expand" process should be done automatically. In other words, the drawings field will be read in containing data that looks like a letter followed by digits 1 - 100 then "thru" and then another letter followed by the digits 1-100. For example, "z1 thru z5". This is the only data that is read and the "expansion" should be done automatically when the "expand" check box is checked.

[privateagentx]

Thanks for you help. I'll try it. I will let you know the final results.

[privateagentx]

Worked like a charm! Here is the final code:


private Sub chkExpand_Click()
If chkExpand.Value then
Dim sDrawings as string
Dim nPos as Integer
Dim lBegin as Long
Dim lEnd as Long
Dim fletter as string
Dim i as Long

List1.Clear

Data3.Recordset.MoveFirst


Do Until Data3.Recordset.EOF

fletter = Left$(Data3.Recordset("drawings"), 1) 'get the first letter of the series

sDrawings = Data3.Recordset("drawings")
nPos = InStr(sDrawings, "thru")

If nPos <> 0 then
lBegin = mid(sDrawings, 2, nPos - 3)
lEnd = mid(sDrawings, nPos + 6)
else
lBegin = mid(sDrawings, 2)
lEnd = lBegin
End If

for i = lBegin to lEnd
List1.AddItem (fletter & i)
next i

Data3.Recordset.MoveNext
Loop
else
List1.Clear
Data3.Recordset.MoveFirst

Do Until Data3.Recordset.EOF
List1.AddItem Data3.Recordset("drawings")
Data3.Recordset.MoveNext
Loop
End If



End Sub