Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Strip extra characters from text string for new ID

Posted on 2012-08-27
5
Medium Priority
?
700 Views
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".

Sandra
0
Comment
Question by:ssmith94015
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 38337785
Following your example: if strString="9113256987"

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

Accepted Solution

by:
lee555J5 earned 2000 total points
ID: 38337806
NewString = Left(PrefixString, 3) & "_" & Mid(ClaimID, 4, InStr(ClaimID, " ") - 1)

Lee
0
 

Author Comment

by:ssmith94015
ID: 38337808
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.

Sandra
0
 

Author Closing Comment

by:ssmith94015
ID: 38337813
That was it, I needed to determine where the space was.  Thank you.

Sandra
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38337819
Correction:
Following your example: if strString = "9113256987 some claim"

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

810 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