Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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