Return text between 2nd and 3rd hyphen (1 Viewer)

yangya · Jun 28, 2018

I think I wasn't really clear with my terminology, so I'll try again.
I'm trying to use Excel to pull data from the query in my Access db (that query is using the custom function called 'TextSplit' - which by the way works perfectly when its run inside Access). It's Excel that's throwing the error 'Undefined function 'TextSplit' in expression' when I use Excel to pull in the data.

I read somewhere about the Jet engine, vba and ado/dao that for whatever reason can't play nice together. If there's no solution to this I guess I'm going to need that "messy expression" using Mid & InStr that was discussed in one of the earlier posts.

Hope this clears everything up. Thanks!!!

isladogs · Jun 28, 2018

It seems to me you've got two good choices.
1. Scrap the SplitText function that Excel doesn't like and just use the Split function as a query expression as in post 11. Now import from Excel assuming Excel is ok with the split function itself which I've not checked.
2. Expert the query data to Excel using the SplitText function or the query split expression

Personally I'd use method 2 as that will definitely be workable

Also one bad choice. Use the messy Mid and InStr approach. The reason being that you'll have to work it out for yourself. I really don't have the time to do so when better solutions exist.

Best of luck

Gasman · Jun 28, 2018

yangya said:
I think I wasn't really clear with my terminology, so I'll try again.
I'm trying to use Excel to pull data from the query in my Access db (that query is using the custom function called 'TextSplit' - which by the way works perfectly when its run inside Access). It's Excel that's throwing the error 'Undefined function 'TextSplit' in expression' when I use Excel to pull in the data.

I read somewhere about the Jet engine, vba and ado/dao that for whatever reason can't play nice together. If there's no solution to this I guess I'm going to need that "messy expression" using Mid & InStr that was discussed in one of the earlier posts.

Hope this clears everything up. Thanks!!!

How are you ' using Excel to pull data from the query'?, as the function works just as well in Excel VBA?

June7 · Jun 28, 2018

I tried intrinsic Split function in query as suggested in post 11 and it fails. Query does not recognize it. And the (2) generates another error. AFIK, Split only works in VBA.

NauticalGent · Jun 28, 2018

June7 said:
I tried intrinsic Split function in query and it fails. Query does not recognize it. And the (2) generates another error. AFIK, Split only works in VBA.

I had the same issue...

isladogs · Jun 28, 2018

June7 said:
I tried intrinsic Split function in query and it fails. Query does not recognize it. And the (2) generates another error. AFIK, Split only works in VBA.

Now I think about it, you're right.
Nothing to prevent yangya exporting to Excel using the SplitText function however.

June7 · Jun 28, 2018

Except we named TextSplit but we know what you meant.

A solution may be to save the parsed strings to actual fields of table. Then Excel can pull from table.

Excel won't handle query that uses Nz() function either. Excel VBA does not recognize Nz(). It does recognize Split(). But it seems custom functions are an issue. I wil have to test later.

isladogs · Jun 28, 2018

June7 said:
Except we named TextSplit but we know what you meant.

LOL - my first version was called TestSplit! Its slowly transmogrifying .....

MarkK · Jun 28, 2018

Here's what the Nz() function is...

Code:

Function Nz(Value As Variant, Optional ValueIfNull As Variant) As Variant
    If IsNull(Value) Then
        Nz = Empty    [COLOR="Green"]'default[/COLOR]
        If Not IsMissing(ValueIfNull) Then Nz = ValueIfNull
    Else
        Nz = Value
    End If
End Function

It is provided by Access as a built-in function, but you can do exactly the same work using the code above.
Mark

yangya · Jun 29, 2018

OK, first, many thanks to all of you who posted. I finally found something that works. From a post by sxschech in a thread called, "Split text into a new column" (post #3), I put the function in Excel and then just created columns in my table using that. I put the [Barcode] field in my access query and then use the function in Excel to break it up. Works fine.

Again, thank all of you for your help.

June7 · Jun 29, 2018

Here is the messy version. Let x represent your field:

PartA: Left(Mid(x, InStr(x, "-")+1), InStr(Mid(x, InStr(x, "-")+1), "-")-1)

PartB: Left(Mid(Mid(x, InStr(x, "-")+1), InStr(Mid(x, InStr(x, "-")+1), "-")+1), InStr(Mid(Mid(x, InStr(x, "-")+1), InStr(Mid(x, InStr(x, "-")+1), "-")+1), "-")-1)

Or if the third part will always be all numbers:
PartB: Val(Mid(Mid(x, InStr(x, "-")+1), InStr(Mid(x, InStr(x, "-")+1), "-")+1))

Be aware all will fail if field is empty. The first PartB requires 3 hyphens.

A Null field can be handled with:

PartA: IIf(x Is Null, "none", <above expression>)

pbaldy · Jun 29, 2018

Post 30 was moderated, I'm posting to trigger email notifications.

isladogs · Jun 29, 2018

I think the solution in post #30 is unnecessarily over-messy...

So I've made time to do it after all ...

In the code below I've defined Barcode as a string.
In yangya's db, this is obviously a field name

Firstly, I'll do it in 3 steps so the method is clearer:

Code:

Sub TestSplit1()

Dim Barcode As String

Barcode = "11.5RT-10B53A-2233-050716-SP-55-105.0-125"

'remove the text string before the first hyphen
Barcode = Mid([Barcode], InStr([Barcode], "-") + 1)

'remove the text string before the first hyphen i.e. second hyphen in the original string
Barcode = Mid([Barcode], InStr([Barcode], "-") + 1)

'remove the text after the first hyphen in the remaining text
Barcode = Left([Barcode], InStr([Barcode], "-") - 1)

Debug.Print Barcode

End Sub

Now do in two steps by combining the 2nd & 3rd steps:

Code:

Sub TestSplit2()

Dim Barcode As String

Barcode = "11.5RT-10B53A-2233-050716-SP-55-105.0-125"

'remove the text string before the first hyphen
Barcode = Mid([Barcode], InStr([Barcode], "-") + 1)

'remove the text string before the first hyphen (i.e. second hyphen in the original string)
'and remove all text after the first hyphen in the remaining text
Barcode = Left(Mid([Barcode], InStr([Barcode], "-") + 1), InStr(Mid([Barcode], InStr([Barcode], "-") + 1), "-") - 1)

Debug.Print Barcode

End Sub

Still messy but perhaps not quite so bad
It would be possible to merge these two steps as well but I'll leave that for anyone else who can be bothered

IMHO, using Split is still far better for this scenario

MarkK · Jun 29, 2018

ridders said:
IMHO, using Split is still far better for this scenario

I agree, which is too short a message, so I repeat, I agree.

June7 · Jun 29, 2018

I also agree but OP wanted to avoid calling custom function in query because Excel could not open a recordset of query using UDF. So now OP has plenty of options.

NauticalGent · Jun 29, 2018

We had a saying in the military logistics world: I give you what you ask for, not what you need...

NauticalGent · Jul 18, 2018

Found this today while I was researching something else:

https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

Pretty nifty...

pbaldy · Jul 18, 2018

Daniel's got some really neat stuff on his site, good resource.

NauticalGent · Jul 18, 2018

pbaldy said:
Daniel's got some really neat stuff on his site, good resource.

Absolutely. This is a little off-topic but I did a GDB on code libraries and his input is about the best I could find. Still not sold on if the juice is worth the squeeze but at least I know how to establish one now.

Any thoughts on this?

Return text between 2nd and 3rd hyphen (1 Viewer)

New member

MVP / VIP

Enthusiastic Amateur

AWF VIP

Ignore List Poster Boy

MVP / VIP

AWF VIP

MVP / VIP

bit cruncher

New member

AWF VIP

Wino Moderator

MVP / VIP

bit cruncher

AWF VIP

Ignore List Poster Boy

Ignore List Poster Boy

Wino Moderator

Ignore List Poster Boy

Similar threads

Users who are viewing this thread