Eliminating last 3 characters of data from each value of an entire column of values

[samdahlr]

Hello,



I have an entire column of values ending in ".00". For example 123456.00. I want to get rid of the last 3 characters, the ".00" from each value for the entire column. I preferably want to do this in SQL language.

Any thoughts?

Thank you

[Bob Fitz]

I believe you can do this with an Update Query, using Mid() and Len() functions in an expression that returns the required values.

[samdahlr]

I am very new to access. The table name is "CAMS" and the column name is "CAMS_ID". If you have the time, could you write an example SQL statement for me please?

Thank you

[lfpm062010]

Using RIGHT(FIELDNAME, 3) will show the last 3 character.
Combined with REPLACE().

It can be done in a update query or select to view.
What Bob Fitz says will work as well.

[AnandKhinvasara]

Select round(cams_id,0) from cams

[Bob Fitz]

I am very new to access. The table name is "CAMS" and the column name is "CAMS_ID". If you have the time, could you write an example SQL statement for me please?

Thank you

Make a backup of your data
then try this:

UPDATE CAMS SET CAMS.CAMS_ID = Mid([CAMS_ID],1,(Len([CAMS_ID])-3));

[RuralGuy]

It is unlikely your field has the ".00" actually stored. It is more likely to be a display format setting.

[samdahlr]

I just need to get rid of the ".00" for the time being so I can combine it with other data in Access that is in 123456 format not 123456.00.

I tried this,

select CAMSID
from CAMS
RIGHT (CAMSID, 3)
REPLACE ()
;

It is saying I have a syntax error in JOIN operation.

[RuralGuy]

If it is a Numeric field then the query will let you set the output format of the field. I would suggest "General" format.

[samdahlr]

I am not seeing an option to set the output format anywhere. I tried using this and it still says syntax error.

Update CAMS
set CAMSID
where RIGHT (CAMSID, 3)
REPLACE ()
;

[Bob Fitz]

Have you tried my suggestion in post #6

[John_G]

What is the data type of the CAMS_ID field in the table? An ID field should be numeric-long or numeric-integer.

Are you trying to actually change data in the table, or only how you see it in a query? To change the display format in a query (for a numeric column), in design view, right-click in the column; from the dropdown select Properties. In the property sheet, set the "Format" to "General Number", and "Decimal Places" to 0.

When you run the query, that column will show the numbers with no decimal places.

John

[samdahlr]

Bob, I tried your statement and it did not get rid of the .00 in the database. Yes, I actually want to change the data in the database to show 123456 instead of 123456.00. Essentially change the format not the actual person's ID. Will changing the properties in design view work for this?

[Bob Fitz]

Can you answer the points raised by John in post #12

[John_G]

Yes, you can change the properties in the table. Set the data type of the CAMS_ID field to Numeric - Long.

You will probably get a warning message about possibly losing some data, but that refers to the parts following the decimal point, which in your case are always 0 (I hope).

Now, if you have a form or query that has the format set to show 2 decimal points, it is still going to do that, even though you changed the data type in the table. You will have to fix the display formats as well.

John