Extract part of string

[Fais]

Hello, i have the following scenario. I want to extract part of text from a string in Access 2010.

i have the following field:

column1:
1234-Main st-1234567890-11218-US-
4321-Not main st-NYC-1234567890-11214-US-



I want the extract '1234567890' from both of the fields above, as you can see it some times falls after the 2nd '-' and sometimes after the 3rd '-', i want to write a logic that if there are 5 '-' total then look after 2nd '-' and if there are 6 '-' total then look after 3rd '-' and extract the number before the next dash. Please assist, thanks.

[pbaldy]

Personally I would create a function that accepted the field as an input and returned the desired portion. You can use the Split() function in the function. The UBound function will tell you how many parts there are, and you can grab the desired portion from there.

[Bob Fitz]

I don't quite understand the criteria for the text required.

... i want to write a logic that if there are 5 '-' total then look after 2nd '-' and if there are 6 '-' total then look after 3rd '-' ....

In the second field the required data is after the third "-" but both fields only have 5 '-'s

[Fais]

My apologies, i have edited the post.

[Fais]

Hi,
thanks for the reply. I would appreciate it if you can give me some sort of a syntax because i am fairly new to Access. thanks in advance.

[June7]

Something like:

Function GetPart(strInput As String) As String
Dim strAry As Variant
strAry = Split(strInput,"-")
GetPart = strAry(IIf(UBound(strAry)=5,2,3))
End Function

Call function from query:

GetPart([fieldname])

This code assumes every record has a value in the field.