Solved

microsoft access custom vba code to manipulate string

Posted on 2014-02-03
2
499 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

807 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