Solved

microsoft access custom vba code to manipulate string

Posted on 2014-02-03
2
491 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
Comment Utility
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
Comment Utility
Thx.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

743 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

16 Experts available now in Live!

Get 1:1 Help Now