[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


format a number to remove dashes?

Posted on 2004-10-25
Medium Priority
Last Modified: 2009-07-29
I have the following query:

SELECT [Litho Data].TTLotID
FROM [Litho Data];

The data looks like:

I need it to look like:   545217522

How do you do that?

Also  - it needs to be formatted to be a number and not a character string.
Question by:JoseDavila
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 18

Accepted Solution

Data-Man earned 1000 total points
ID: 12406317
SELECT clng(Replace([Litho Data].[TTLotID],"-",""))
FROM [Litho Data];

Try that....Mike


Assisted Solution

CrossWired earned 300 total points
ID: 12406322
Check this out


To convert it to an integer use CInt()

LVL 16

Assisted Solution

GreymanMSC earned 700 total points
ID: 12406570
Jay, CInt converts numeric string values to Integers, this is true, but only if they are short integers.  545217522 is too large to be an Integer value and thus CInt("545217522") will throw an overflow exception!  
CLng converts numeric string values to Long Integer and is more appropriate for values of this range.  
(PS: CDbl will convert such values into Double precision floating point values, if that is preferred.)

Expert Comment

ID: 12406670
yeah, i caught that as me and data-man posted almost simealtaneously, but since h already had it i didn't correct myself

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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