Strip extra characters from text string for new ID

Posted on 2012-08-27
Last Modified: 2012-08-27
I have an ACCESS 2003 process that brings in claim data.  The claim id is prefixed by three characters which need to be ignored, then the next string of characters to the first space need to be appended to a prefix the database recognizes.  The number of charactesr after the first three can vary so I cannot simply grab so many characters after character 3 in the string.

For example, the string "9113256987 some claim" needs to ultimately be "RDB_3256987".

Question by:ssmith94015
    LVL 30

    Expert Comment

    Following your example: if strString="9113256987"

    newString= "RDB_"& right(strString,len(strString)-3)
    LVL 13

    Accepted Solution

    NewString = Left(PrefixString, 3) & "_" & Mid(ClaimID, 4, InStr(ClaimID, " ") - 1)


    Author Comment

    hnasr, problem is the first characters are in a string of a description so I need to stop at the first space in the string.  As in the example, the descritpions can be:

    "9113256987 some claim"

    Thus, the first characters can be any length, so I need to determine the position of the space.


    Author Closing Comment

    That was it, I needed to determine where the space was.  Thank you.

    LVL 30

    Expert Comment

    Following your example: if strString = "9113256987 some claim"

    newString = "RDB_"& val(right(strString,len(strString)-3))

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    729 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