Solved

microsoft access custom vba code to manipulate string

Posted on 2014-02-03
2
496 Views
Last Modified: 2014-02-08
I need help in extracting and formatting a column that contains text.  The output needs to be:

RGXXXXXX  (RG followed by a 6 digit number).

The data is stored in various flavors.  I have attached a list on some of the variants.  So in the example below:

RGO: 277803
S/N: 58029644

The output should be RG277803 after running some public function that I can call in a MS Access query.  I am new to this and could really use some expert help.

Thanks.
Book1.xls
0
Comment
Question by:sxxgupta
2 Comments
 
LVL 1

Accepted Solution

by:
MarvinM80 earned 500 total points
ID: 39830726
Your sample is in Excel, but you want an Access function. Right?
First, you want to determine the format that your input is in. It appears that we can differentiate on the space character that occurs in the 3rd position or the 4th position or not at all. We can use a CASE statement for this.

Dim strIn As String
Dim strOut As String

strIn = Your Input String

Select Case InStr(1,strIn," ")

CASE 3
strOut = Mid(strIn, 1, 2) & Mid(strIn, 4, 6)

CASE 5
strOut = Mid(strIn, 1, 2) & Mid(strIn, 6, 6)

CASE Else
strOut = Mid(strIn, 1, 8) 

End Select

Open in new window

0
 

Author Closing Comment

by:sxxgupta
ID: 39844397
Thx.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

813 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

12 Experts available now in Live!

Get 1:1 Help Now