Removing precision from a number import

[jimkent]

I have a machine that imports a set of number fields that for some reason pulls in decimal points and thousands seporators from an ODBC connection. The same data and program imports into another machine just fine. My question is, is there any way to remove the commas from a number field? the decimal point was easy to fix (truncate) but the commas are a bit difficult. Thanks.

[IdealData]

Check out the FILTER function.

[LaRetta]

Commas are acceptable in number fields. If Users ever touch this data, you can bet commas will come back. I wonder why you feel the need to remove either the decimal or the comma, as both are acceptable number characters? BTW, I hope you don't have any numbers such as .499. Truncate will produce .49. Round() might be more appropriate ... but then Round() wouldn't remove the decimal.

I've just never had the need nor desire to remove either comma or decimal or ending zeros and wonder if it's necessary? I know some people even want to ADD zeros. I prefer to use field format for such things. If I needed a specific export format, I would handle it at the time of export. I want to learn. Can you explain your reasoning in this? Thanks! :wink2:

[jimkent]

In this case I'm importing from a SQL database and one of the fields I import is a productID field. Not good to have productID 12,345.00. I export some of the fields to an XML file that gets shipped out to clients. They have formating issues with it because the product id is now 12,345.00 rather than 12345. That's the issue. The other field is a jersey number (like on a football jersey) and they don't want to see jersey number 23.00 on there export. I did change the decimal issue but I'm having trouble with the commas. I'll check out the filter function and see what that does. Thanks, Jim

[comment]

Somethings is wrong here. This shouldn't happen, and the fact that it only happens on a certain system just stresses the point.

I don't know if it's possible that the same SQL DB would send different data to different systems, but I would suspect the field types are wrong on this particular one. On my system, for example, Truncate ( "1,234.56" ; 0 ) returns 1234 with no comma. If the calc result is set to Number, you don't even need Truncate(), Filter(), or anything. A simple = ProductID should remove all decimal formatting.