Use a query to split a table field into two fields based on delimiter or lack thereof

[zdjbel]

Hello,

I need help with using a query to split a table field into two fields. Some of the entries have a "-" delimiter while others don't. I need those that don't have the delimiter to stay intact and the ones that do have it to move them to a new field and delete the delimiter "-".

Initial state		Desired result
OrderNumber		OrderNumber	Modification
19RB1018S0009	split to:	19RB1018S0009
19RB1018S0006	split to:	19RB1018S0006
19RB1018P0013	split to:	19RB1018P0013
19RB1018S0010	split to:	19RB1018S0010
19RB1018F0001	split to:	19RB1018F0001
19RB1018P0037	split to:	19RB1018P0037
19RB1018S0011	split to:	19RB1018S0011
SRB10017L1126-M001	split to:	SRB10017L1126	M001
SRB10015V0306-M001	split to:	SRB10015V0306	M001
19RB1018S0012	split to:	19RB1018S0012

Thank you in advance.

[Welshgasman]

Use the Split function.
However you cannot use Split directly in a query, but will need to create a wrapper function.
Once created, you use it again and again.

Create a Select query first to make sure you have it working correctly.

[zdjbel]

Thank you for your suggestion. I tried making a public function and calling it from the query. Works fine on extracting the Order number itself but when I add the Modification field to the query and call the function, the database goes into debugging mode and freezes up completely. Here's what I have:

Public Function MySplit(TextString As String, ItemNumber As Long) As String


MySplit = Split(TextString,"-")(ItemNumber)


End Function


Query Field 1: Order:MySplit(OrderNumber,0)
Query Field 2: Order:MySplit(OrderNumber,1)


I understand that it freezes because there isn't a second delimiter "-" in the string being queried (or so I think), but I don't know how to "tell it" to ignore the fact that there isn't a delimiter at the end of the string so give me just the characters after the delimiter. Any suggestions?

Thank you.

[Minty]

You could also do it with the instr function in a query:

SELECT test.OrderNumber,
IIf( InStr(1,OrderNumber,"-") > 0 , Left([OrderNumber], InStr(1,OrderNumber,"-")-1),OrderNumber) AS NewOrdNo,
IIf( InStr(1,OrderNumber,"-") > 0 , Mid([OrderNumber], InStr(1,OrderNumber,"-")+1),Null) as Modification
FROM test;

Gives you

OrderNumber	NewOrdNo	Modification
19RB1018S0009	19RB1018S0009
19RB1018S0006	19RB1018S0006
19RB1018P0013	19RB1018P0013
19RB1018S0010	19RB1018S0010
19RB1018F0001	19RB1018F0001
19RB1018P0037	19RB1018P0037
19RB1018S0011	19RB1018S0011
SRB10017L1126-M001	SRB10017L1126	M001
SRB10015V0306-M001	SRB10015V0306	M001
19RB1018S0012	19RB1018S0012

[Welshgasman]

You would need to test how many in the array?
Perhaps the Instr is better for you.

[zdjbel]

Thank you Minty, that did the trick. I'm only an occasional Access user and I would never have come up with this on my own. Thanks a million!

[zdjbel]

Hello Welshgasman,

Minty's Instr code worked and is indeed better (easier) for me as you suggested. Thank you for your efforts to help me with this.