Return text between 2nd and 3rd hyphen

[yangya]

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]

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]

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]

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]

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]

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]

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]

Except we named TextSplit but we know what you meant.

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

 

[MarkK]

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

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]

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]

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]

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

 

[isladogs]

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:

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:

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]

IMHO, using Split is still far better for this scenario

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

 

[June7]

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]

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

 

[NauticalGent]

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]

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

 

[NauticalGent]

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?