Parsing and Storing values in a specific field

[Tarbuza]

I have a text in the following format:

#Vol.1#Pg.132#December 2002#Source:ABC
##Pg.140#January 2001#

How can I parse it through query or VBA whichever is easy and store values as follows:

First one after # sign should go in a field called Volume (for instance Vol.1). The second one after # should go into a field called Page (for instance Pg.132. The third one after # should into a field called PubDate field (for instance: December 2002) and the fourth field should go into a field called source (for instance ABC).

If you look at the second one, sometimes there won't be value.

All these values are in a Microsoft Access format in one single field called PubText.

Any helps would be greatly appreciated.

 

[scking]

You could use the 'Split' function to place the value of the field, minus the # front and end delimiters, into an array and handle it through array suscripts. The subscript 0 would be the first value, etc. This would work effectively through VBA only and place it into some type of a control or what?

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[Tarbuza]

Unfortunately, I don't know Access VBA. I wrote small codes for Word VBA.

Do you have a jumpstart code that can help me get starting? Thanks for all your helps.

 

[Passenger]

Hi,

I've noticed that the use of the "#" isn't consistent - you can write some parsing code if you make sure that each line is starting with an "#" and is closed with a "#"; also be sure that between each value the delimiter-sign is used.
Once you have done this, you can use a function like this :

Public Sub Oplos(Mystring As String)
Dim pos(4) As Integer
Dim MyValues(4) As String
Dim i, j, counter, startpos As Byte

teller = 0
For i = 2 To Len(Mystring) Step 1
If Mid$(Mystring, i, 1) = "#" Then
counter = counter + 1
pos(counter) = i
End If
Next i
startpos = 2
For j = 1 To teller
MyValues(j) = Mid(Mystring, startpos, pos(j) - startpos)
startpos = pos(j) + 1
'Write here what you want to do with the results
'Just to show you what the values are, I've used Msgbox
MsgBox waarden(j)
Next j
End Sub

Good luck

 

[Passenger]

Sorry, made some errors while translating my code in the early post. Here we go...

Public Sub Oplos(Mystring As String)
Dim pos(4) As Integer
Dim MyValues(4) As String
Dim i, j, counter, startpos As Byte

counter = 0
For i = 2 To Len(Mystring) Step 1
If Mid$(Mystring, i, 1) = "," Then
counter = counter + 1
pos(counter) = i
End If
Next i
startpos = 1
For j = 1 To counter
MyValues(j) = Mid(Mystring, startpos, pos(j) - startpos)
startpos = pos(j) + 1
'Write here what you want to do with the results
'Just to show you what the values are, I've used Msgbox
MsgBox MyValues(j)
Next j
End Sub

 

[scking]

Actually, if writing the code to parsing the string you can operate from any rules you want. You don't even need to delimit the end because with a text box the end would be the end. In the following I used Tarbuza's sample textbox string as a constant and put each value in a variant array. It would be extremely simple to add a parameter to the subroutine to replace the constant.

Public Sub SplitIt()
Dim myArray As Variant
Const val As String = "#Vol.1#Pg.132#December 2002#Source:ABC##Pg.140#January 2001#"
'Strip the front and end
strValue = Mid$(val, 2, Len(val) - 2)
myArray = Split(strValue, "#&quot
End Sub

'From the immediate window the following values were displayed
?myarray(1)
Pg.132
?myarray(2)
December 2002
?myarray(3)
Source:ABC
?myarray(4)

?myarray(5)
Pg.140
?myarray(6)
January 2001
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[vbajock]

You know, I really like that, scking.

 

[Tarbuza]

Thanks everyone for all your helps. It's working like a charm. The only problem that I am encountering is sometimes it gives "subscript out of range". The code is as follows. I tried putting On Error Goto but it doesn't work. Is there a way to trap subscript out of range? Thanks again for any helps.

Public Sub SplitIt()
On Error GoTo nxt
Dim db As Database
Dim rs As DAO.Recordset
Dim myArray As Variant
Dim val As String
Set db = CurrentDb
Set rs = db.OpenRecordset("BookRef", dbOpenTable)
rs.MoveFirst
Do While Not rs.EOF
mval = rs!Text
'Strip the front and end
strValue = Mid$(mval, 1, 1)
rs.Edit
mdollar = False
If strValue = "#" Then
rs!a = ""
nmval = Mid$(mval, 2, Len(mval) - 2)
mdollar = True
Else
nmval = mval
End If
myArray = Split(nmval, "#&quot
If mdollar Then
rs!a = ""
rs!b = myArray(0)
rs!c = myArray(1)
rs!d = myArray(2)
rs!e = myArray(3)
Else
rs!a = myArray(0)
rs!b = myArray(1)
rs!c = myArray(2)
rs!d = myArray(3)
rs!e = myArray(4)
End If
'myArray = Split(val, "#&quot
'MsgBox (myArray(0))
nxt:
rs.Update
rs.MoveNext
Loop
rs.Close
End Sub

 

[scking]

The way you have it is that the variable with the #'s stripped from the front and back must have 3 #'s if mdollar otherwise it must have 4 #'s. It is best to verify these types of information about the variable whenever you are using hard coded subscripts.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[Tarbuza]

Thanks for your reply.

I am not clear what you mean by:

"It is best to verify these types of information about the variable whenever you are using hard coded subscripts."

Do you mean to check UBound?

 

[scking]

Ok, so split takes a variable and places it's parts into an array based on how many delimiters there are. If the variable ##### where send and the first and last delimiters stripped the array would have 4 items which could be addressed by a subscript (0-4). When you use hard coded,

rs!b = myArray(0)
rs!c = myArray(1)
rs!d = myArray(2)
rs!e = myArray(3)

you are ASSUMING that the original variable contains 5 delimiters. NEVER assume in code. ALWAYS verify that the variable is as you expect it to be and if it isn't then either provide the user with an error message and allow them to skip over the invalid variable or create a log file of invalid variables.

Tests are:
If mdollar then the variable with the front and end delimters MUST have 3 delimiters remaining to create the array of 4 subscripted elements. Else the variable MUST have 4 delimiters remaining. If it has any less then you will get an error 6, invalid subscript, when trying to address something that is not there.



-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[Tarbuza]

I am getting the following error message and that's why I posted it to find out how can I skip the subscript out of range and go to next record. I tried to use ON ERROR GOTO nxt but somehow it is not trapping it. How can I trap subscript out of range?

"If it has any less then you will get an error 6, invalid subscript, when trying to address something that is not there."