Solved

Parse a String Multiple Times

Posted on 2013-06-20
3
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

691 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