Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

How to change type of populated field in Base?

I have a table in Base ( Win 7, LO Portable v 4.3.2.2, US English) that is populated. It has 5 digit zip codes in it. I initially set it as Integer, not realizing that leading 0's would be removed by the database engine. It now has about 1500 records in it. I need to change the field type, probably to Text. How best to do it? What type would work best?

If I try to edit the table and change the type I get an error that it can't be done and would I like to remove the field and append a new one. Accepting that wipes out the data in the field.

I think an approach would be to created a new field, with a better choice of field type and a similar name. How would I then populate it with the existing data? If I can somehow copy it over, I can easily look for the 4 digit entries and prepend a 0 to them manually. I'm guessing this may need some (simple) SQL. If so, can someone tell me the SQL?

Also, I am assuming that once I have created the new field, populated it, and edited it, I can then delete the original and rename the replacement to match the original. Am I correct in thinking that all existing references, such as in queries and reports to the original field will now point to the renamed new field?

TIA!

How to change type of populated field in Base?

I have a table in Base ( Win 7, LO Portable v 4.3.2.2, US English) that is populated. It has 5 digit zip codes in it. I initially set it as Integer, not realizing that leading 0's would be removed by the database engine. It now has about 1500 records in it. I need to change the field type, probably to Text. How best to do it? What type would work best?

If I try to edit the table and change the type I get an error that it can't be done and would I like to remove the field and append a new one. Accepting that wipes out the data in the field.

I think an approach would be to created a new field, with a better choice of field type and a similar name. How would I then populate it with the existing data? If I can somehow copy it over, I can easily look for the 4 digit entries and prepend a 0 to them manually. I'm guessing this may need some (simple) SQL. If so, can someone tell me the SQL?

Also, I am assuming that once I have created the new field, populated it, and edited it, I can then delete the original and rename the replacement to match the original. Am I correct in thinking that all existing references, such as in queries and reports to the original field will now point to the renamed new field?

TIA!