Solved

I need a query that formats a set of numbers.

Posted on 2008-06-23
5
198 Views
Last Modified: 2011-10-19
I just need an Access Query that changes the format of a set of numbers like this

08-01-   6-0059-0000-0000

into this

0801-0006-0059-0000-0000

Thank you
0
Comment
Question by:GravitaZ24
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 21847900
If your first set of numbers is always in that format, then this should work for you:

NewFormat: Left([YourNumberField],2) & Mid([YourNumberField],4,2) & "-000" & Mid([YourNumberField],8,1) & "-" & Right([YourNumberField],14)

Flyster
0
 

Author Comment

by:GravitaZ24
ID: 21848710
I'm sorry that worked for the first few numbers, but there was more to it than I realized, I attached a sample of the numbers, I have to get them into this format xxxx-xxxx-xxxx-xxxx-xxxx-xxx. The second groups of numbers is where i'm having trouble, they have to be filled in with zeros where there are blanks.
0
 

Author Comment

by:GravitaZ24
ID: 21848716
I forgot to attach to the last post, here it is
Sample.xls
0
 
LVL 22

Accepted Solution

by:
Flyster earned 125 total points
ID: 21849440
See if this works for you:

SELECT Sheet1.[Unique], Replace([Unique],"-","") AS Step1, Replace([Step1]," ",0) AS Step2, Format([Step2],"0000-0000-0000-0000-0000-000") AS Step3
FROM Sheet1;
0
 

Author Closing Comment

by:GravitaZ24
ID: 31469815
Thank you! that worked!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A short article about a problem I had getting the GPS LocationListener working.
This is an explanation of a simple data model to help parse a JSON feed
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 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

25 Experts available now in Live!

Get 1:1 Help Now