Microsoft Access

In Excel, there is a feature that allows you to convert data in a column into multiple columns by using the Text to Column feature.
Is there a similar feature in Access?   My table currently includes a column with account numbers.  Unfortunately the column also has a preceding 0 on each row that I would like to remove.
ArisaAnsarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
ArisaAnsar,

Access has no automated Text to Columns feature, but one can (mostly) replicate the functionality with queries and code.

As for eliminating a leading zero, this will remove a single leading zero on a text field:

SELECT IIf(AcctNum Like "0*", Mid(AcctNum, 2), AcctNum) AS xAcctNum
FROM SomeTable

Open in new window


or

UPDATE SomeTable
SET AcctNum = Mid(AcctNum, 2)
WHERE AcctNum Like "0*"

Open in new window


Patrick
0
Patrick MatthewsCommented:
If you have multiple leading zeroes and need to eliminate all of them, you need code.

For example, add this to your VBA project:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll
    ' controls whether all instances of the matched string are replaced (True) or just the first
    ' instance (False)
    
    ' If you need to replace the Nth match, or a range of matches, then use RegExpReplaceRange
    ' instead
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
End Function

Open in new window


Then use it like this:

SELECT RegExpReplace(AcctNum, "^0+") AS xAcctNum
FROM SomeTable

Open in new window


or

UPDATE SomeTable
SET AcctNum = RegExpReplace(AcctNum, "^0+")
WHERE AcctNum Like "0*"

Open in new window



That approach uses Regular Expressions, as explained here: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
0
ArisaAnsarAuthor Commented:
Thanks for helping.  I appreciate it.
I don't know how to use VBA in access.  Is there a way to extract the zero using the right or left function?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Patrick MatthewsCommented:
ArisaAnsar,

Please see my first post, which explains how to trim a single leading zero without any VBA at all.

Patrick
0
ArisaAnsarAuthor Commented:
Sorry - Please be a little patient with me.  Can you advise where I should add the codes?  I'm a new user to Access.  Do I post in the criteria of the account column or in the Field row and replace AcctNum with my column name?

UPDATE SomeTable
SET AcctNum = Mid(AcctNum, 2)
WHERE AcctNum Like "0*"
0
ArisaAnsarAuthor Commented:
Here is a sample of the database.  Thanks again.
Relationship-Database.mdb
0
Patrick MatthewsCommented:
ArisaAnsar,

In your database, open your query, and switch to the SQL view.

If your account number always has a single leading zero, paste this in:

SELECT Mid(Account, 2) AS xAccount, Statements, Prospecturs, [T-1099], Confirms
FROM [E-Delivery];

Open in new window


If your account number sometimes, but not necessarily always, has a single leading zero, paste this in:

SELECT IIf(Account Like "0*", Mid(Account, 2), Account) AS xAccount, Statements, Prospecturs, [T-1099], Confirms
FROM [E-Delivery];

Open in new window


Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArisaAnsarAuthor Commented:
Thank you!  This worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.