Field Data Truncation

[JeffGeorge]

Is there a way to truncate existing data in a given field in a table? Specifically, I have a field in which I want to truncate that value for each record to the rightmost 7 characters. Is that possible without having to export, modify, import?

[orange]

You could build and execute an Update query

[JeffGeorge]

So, if I understand correctly, I can use the truncate function to update that field for all records within a query. I've never tried that, but I will. Thanks for the input.

[Bulzie]

I would create a new field and do the update to that new field. If you mess up you still have your original column. Once it looks good you can then delete the old one (or keep it) and rename the new one, etc.

Look up Right Function.

[JeffGeorge]

I created a test database of one table with two fields of text type. I created an update query in Access that produced the following SQL:

UPDATE Table1 SET Table1.Field2 = Right([Field1],7);

This test produced the desired results. However, that raised two new questions:

1) Can I substitute SET Table1.Field1 for SET Table1.Field2 and basically update the field in place?
2) I've found out I have to do this in an MS SQL database. I duplicated my experiment in SQL Server Management studio with UPDATE Table1 SET Field2=RIGHT(Field1,7) but this turns Field2 into blank values. What am I missing?

[JeffGeorge]

Never mind that last question. Apparently I had a bunch of trailing spaces in my test field. That's why I was seeing nothing.

[Bulzie]

I believe you can update the same field with the new value. But again there is some risk to that such as what if your program crashes, loses connection to SQL in the middle of the update, etc. I always work about data getting lost. Maybe create a field called Field1_Original, do an update query first to move the values from Field1 to Field1_Original, then use 2nd Update query to change the value in Field1?