Parsing Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Afternoon

I have a table that has a reference field with 3 diffrent values in it that I need to parse out into 3 seperate fields. Listed below is the example of the data

Order Number :5202518 Delivery No : 483385 Line No : 22

I need to parse 5202518 into one field, 483385 into a second field and 22 into another. Can anyone assist in this matter

Regards

Tony
 
Sure, your code can go:

Public Sub test1()

Dim v As Variant
Dim strTest As String

Dim OrderNum As String
Dim DeliveryNum As String
Dim LineNum As String

strTest = "Order Number :5202518 Delivery No : 483385 Line No : 22"

v = Split(strTest, ":")

OrderNum = Split(v(1), " ")(0)
DeliveryNum = Split(v(2), " ")(1)
LineNum = Trim(v(3))

Debug.Print "ordernum = " & OrderNum
Debug.Print "Delivery Num = " & DeliveryNum
Debug.Print "line num = " & LineNum


End Sub


The only real problem you will encounter is if there is a "space" before the
:, or not. You will thus have to adjust the above code based on this issue.

Try pasting the above code into a module...and run it....

You can then use the above for the basis of a processing loop (only another
few lines of extra code).
 
Thanks Albert! Unfortunatley, I forgot to mention that I was trying to parse this data within a query. Doh! Sorry.... Do you know how to parse with a query


----- Albert D. Kallal wrote: ----

Sure, your code can go

Public Sub test1(

Dim v As Varian
Dim strTest As Strin

Dim OrderNum As Strin
Dim DeliveryNum As Strin
Dim LineNum As Strin

strTest = "Order Number :5202518 Delivery No : 483385 Line No : 22

v = Split(strTest, ":"

OrderNum = Split(v(1), " ")(0
DeliveryNum = Split(v(2), " ")(1
LineNum = Trim(v(3)

Debug.Print "ordernum = " & OrderNu
Debug.Print "Delivery Num = " & DeliveryNu
Debug.Print "line num = " & LineNu


End Su


The only real problem you will encounter is if there is a "space" before th
:, or not. You will thus have to adjust the above code based on this issue

Try pasting the above code into a module...and run it...

You can then use the above for the basis of a processing loop (only anothe
few lines of extra code)
 
Sure, just make a function, and use that in the query:

Public Funciton gOrderNum(vString as varient) as varient

dim vTemp as varient

if isnull(vString) = true then
exit funciton
end if

vTemp = split(vString),":")(1)
gOrderNum = split(vTemp(1)," ")(0)

end fucntion

You can then use the above expression gOrderNum([fieldToParse]) in the
query.....

So, you will need to write 3 functions. Just place them in a standard
module..and then you can use them in the query...
 
Back
Top