Pull string from in between characters

[berkdad]

Anyone know an easy way to pull these colors out separately? There isn't always 5 colors. could be two and sometimes even 7 colors in one string. But they will always be separated by "|".

Orange|Yellow|Blue|Violet|Copper

Thanks in advance

 

[Minty]

Have a look at the Split() function, it's only available in VBA - can't be used directly in a query but will do what you want.

This might help - https://access-programmers.co.uk/forums/showthread.php?t=254839

 

[jdraw]

Suggest Split()
Here's a demo of the Split function

Sub showSplit()
    Dim x As String
    Dim i As Integer
    Dim vArr As Variant
    x = "Orange|Yellow|Blue|Violet|Copper"
    vArr = Split(x, "|")
    For i = LBound(vArr) To UBound(vArr)
        Debug.Print vArr(i)
    Next
End Sub

Result:

Orange
Yellow
Blue
Violet
Copper

Good luck.


OOoops: minty is too quick.

 

[Minty]

OOoops: minty is too quick.

First time in a very long time

 

[berkdad]

Suggest Split()
Here's a demo of the Split function

Sub showSplit()
    Dim x As String
    Dim i As Integer
    Dim vArr As Variant
    x = "Orange|Yellow|Blue|Violet|Copper"
    vArr = Split(x, "|")
    For i = LBound(vArr) To UBound(vArr)
        Debug.Print vArr(i)
    Next
End Sub

Result:

Orange
Yellow
Blue
Violet
Copper

Good luck.


OOoops: minty is too quick.

Awesome, thank you. You're not for hire are you? Working on a small project with a bunch of logic and parameters.

 

[isladogs]

Have a look at the Split() function, it's only available in VBA - can't be used directly in a query but will do what you want.

There is a very simple method of making the Split function available for use in a query....wrap it in another function.
For example see https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

 

[jdraw]

Not for hire, but the forum will help if you post clear questions and give an honest effort.

 

[Minty]

There is a very simple method of making the Split function available for use in a query....wrap it in another function.
For example see https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

I could call pedantry on you there Colin
I did say directly...

Hope you are well.

 

[isladogs]

Hi Minty.
I'm fine thanks. Hope all is good in your new job.
It took me a long time to realise that simple workaround but I'm happy to accept the 'accolade'.

 

[theDBguy]

Hi berkdad. Welcome to AWF!

If you're looking for someone to hire, maybe I could help you. Please send me an email (link below) if you want to discuss it as a possible option for you.

Cheers!

 

[berkdad]

Hi berkdad. Welcome to AWF!

If you're looking for someone to hire, maybe I could help you. Please send me an email (link below) if you want to discuss it as a possible option for you.

Cheers!

looks like i need 10 posts in order to see links and attachments. This is my 3rd. I've actually been a member on here before and have a bunch of posts but it's been a while and not sure what email I used, so i created a new account. Not sure if it's appropriate for me to post my email. If so, i will. Let me know