Field with comma

[John liem]

I have a table with text field which contents multile Sales Order numbers (SO 1234, SO 4567, SO 8901) and customer name (text), is there a way to split those SO numbers and get them in separate lines with the same customer's name?. Example:
Customer SO
Me SO 1234
Me SO 4567
Me SO 8901

 

[dcx693]

There is no super-duper easy way to do this. The best way to tackle this (if the data is any longer than like, 50 lines) is to write some VBA code to do it.

The VBA code would separate the SO numbers based on where the commas were and write each one to a different line in another table along with the correct customer name.

 

[John liem]

Do you have an example how to do this? Thanks in advance.
John.

 

[dcx693]

Which version of Access are you using? Have you ever programmed in VBA?

 

[John liem]

I have MS-Access 2000 and yes, I know a little bit about VBA.
Thanks.

 

[Ron_dK]

John,

what happened to this thread. Any follow up.
I would be interested to see some VBA programming
related to this issue , maybe from dcx 693

RAK

 

[John liem]

Hi Rak,

No I haven't got any answers yet to this thread. Sorry!

 

[raskew]

Here's a potential starting-point for you. Example from the debug window:
x = "SO 1234, SO 4567, SO 8901"
? quickparse(x, ",")
SO 1234
SO 4567
SO 8901

...with function quickparse() being:

Function quickparse(ByVal pInput As String, pDelim As String)
'This procedure extracts words/expressions from a string.

Dim texthold As String, textsay As String
Dim n As Integer
n = Len(pInput)
texthold = pInput
n = 0
Do While InStr(texthold, pDelim) > 0
   textsay = Trim(Left(texthold, InStr(texthold, pDelim) - 1))
   'add code to do something
   Debug.Print textsay
   texthold = Trim(Mid(texthold, InStr(texthold, pDelim) + 1))
   n = Len(texthold)
Loop
textsay = texthold
'add code to do something
Debug.Print textsay

End Function