Pull string from in between characters (1 Viewer)

B

berkdad

New member

Local time

Today, 08:11

Joined

Jan 21, 2020

Messages

3

• Jan 21, 2020

• #1

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

AWF VIP

Local time

Today, 16:11

Joined

Jul 26, 2013

Messages

10,366

• Jan 21, 2020

• #2

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

Super Moderator

Staff member

Local time

Today, 11:11

Joined

Jan 23, 2006

Messages

15,379

• Jan 21, 2020

• #3

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

Code:Copy to clipboard

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

AWF VIP

Local time

Today, 16:11

Joined

Jul 26, 2013

Messages

10,366

• Jan 21, 2020

• #4

jdraw said:

OOoops: minty is too quick.

Click to expand...

First time in a very long time

 

B

berkdad

New member

Local time

Today, 08:11

Joined

Jan 21, 2020

Messages

3

• Jan 21, 2020

• #5

jdraw said:

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

Code:Copy to clipboard

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.

Click to expand...

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

 

isladogs

MVP / VIP

Local time

Today, 16:11

Joined

Jan 14, 2017

Messages

18,209

• Jan 21, 2020

• #6

Minty said:

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.

Click to expand...

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

Super Moderator

Staff member

Local time

Today, 11:11

Joined

Jan 23, 2006

Messages

15,379

• Jan 21, 2020

• #7

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

 

Minty

AWF VIP

Local time

Today, 16:11

Joined

Jul 26, 2013

Messages

10,366

• Jan 21, 2020

• #8

isladogs said:

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/

Click to expand...

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

Hope you are well.

 

isladogs

MVP / VIP

Local time

Today, 16:11

Joined

Jan 14, 2017

Messages

18,209

• Jan 21, 2020

• #9

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

I’m here to help

Staff member

Local time

Today, 08:11

Joined

Oct 29, 2018

Messages

21,449

• Jan 21, 2020

• #10

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!

 

B

berkdad

New member

Local time

Today, 08:11

Joined

Jan 21, 2020

Messages

3

• Jan 22, 2020

• #11

theDBguy said:

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!

Click to expand...

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