Improve company productivity with a Business Account.Sign Up

x
?
Solved

Parse a String Multiple Times

Posted on 2013-06-20
3
Medium Priority
?
334 Views
Last Modified: 2013-06-20
Hello,

I have a field that is designed as follows:
          ??/#######/????????/########/???
I would like to parse the field based on the "/".  The field is not alway consistant, so I would like to have the complete string show in the first parse, if the "/" is not available.

I have been tring the Left$ function:
          Left$([Field Name],InStr(2,[Field Name],"/")-1)
This was able to bring in the first part.  Yet it gives me an error of "Func!" if the "/" is not in the string and I have not been successful in tring to retrieve the second portion.

Any help would be  greatly appreciated.
0
Comment
Question by:ammiewinds
3 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39262609
It's not really clear what your aim is here, but the simple way is to use the split() function.

dim myarray
myarray= split(fieldname, "/")
'first value myarray(0), then myarray(1) etc
0
 
LVL 50

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39262637
I use a function (fnParseText) that looks something like:
Public Function fnParseText(ParseWhat as Variant, Element as integer, Optional Delimiter as string = ",") as Variant

    Dim myArray() as string

    myArray = Split(ParseWhat, Delimiter)

    if Element < 0 OR Element > ubound(myArray) + 1 then
         fnParseText = NULL
    Else
         fnParseText = myArray(Element - 1)
    End If

End Function

Open in new window

Then, in your query you would use:

SELECT fnParseText([YourField], 1, "/") as FirstElement
           , fnParseText([YourField], 2, "/") as SecondElement
           , fnParseText([YourField], 3, "/") as ThirdElement
FROM yourTable
0
 

Author Closing Comment

by:ammiewinds
ID: 39263137
Thank you so very much!  Now I'm happy I joined the club.

Jumping up and down!!!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question