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!