Access query split function

Joel Allen
Joel Allen used Ask the Experts™
on
Hello,

In an Access query in design mode, I need a simple way to split a string.  It will have more than three values.

Let's say my field Apple = "Hello,my,name,is,joel"

I want to do this:

Field A: Split ([Apple], "," 4)

The result should be "is".

Thanks for your help,
Joel
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
From:
https://access-programmers.co.uk/forums/showthread.php?t=217471

Tweak the function to:
Public Function ParseText(TextIn As String, SplitChar As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, SplitChar, -1)
ParseText = var(X - 1)
End Function

Open in new window


Then this will run:
SELECT Apple, ParseText(Apple,",",4)
FROM Table1;

Open in new window

Author

Commented:
I'm sorry, I forgot how to get the following inserted into a function.  Can you help me?  I understand the second part though.

Public Function ParseText(TextIn As String, SplitChar As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, SplitChar, -1)
ParseText = var(X - 1)
End Function

Author

Commented:
Oh wait!  I figured it out.  Thank you!

Author

Commented:
Thank you.  It worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial