Solved

Parse a String Multiple Times

Posted on 2013-06-20
3
322 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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now