Copy data from Field to Field

[IannWard]

Hi All

I have a field in my table containing numbers ranging from 00000001 to 00039999, the zeros are there because my numbers could go to 99999999.

I set the format of the Field to Number with the format as 00000000 to allow zeros to be displayed, as when I import the numbers, access was removing the zeros, however, when I search or print report containing the numbers, the zeros are missing and is causing problems when searching in a form. If I search for 00001245 I get no info back, whereas if I search for 1245 I get the details I want. I noticed however, that If I format the field to text, it keeps the zeros when importing new data and all works well.

The problem I have is how to copy the data into another field without losing the zeros but adopting the new Text format? I tried just changing the format of the field which was fine but it removed all the zeros. I have sorted out the format on the spreadsheets I was importing from so not doing it now, but I have 25000 entries I need to change.

Hope you guys understand what I mean.

Thanks in advance for any help.

PS, non sql solutions please as i'm a newbie ish...

 

[CJ_London]

numbers do not contain preceding zeros and the format property is not imported - if you want to search including the preceding zeros, you need to convert your number to text - with all that implies (i.e. unable to add/subtract etc). however usually of you have preceding zeros this implies you would not want to be doing that anyway.

 

[IannWard]

numbers do not contain preceding zeros and the format property is not imported - if you want to search including the preceding zeros, you need to convert your number to text - with all that implies (i.e. unable to add/subtract etc). however usually of you have preceding zeros this implies you would not want to be doing that anyway.

The new field is set to Text but as I copy them they lose the zeros as this was just the format of the original field and not the number itself. Could I, in a field formatted as Text run something that takes all numbers wether they have 2, 3 or 4 characters and convert them to an 8 digit number? IE, 1234 becomes 00001234.

Thanks

 

[arnelgp]

after you copy it will lose its format, then just make query to update the field in the table

UPDATE table SET field = Format(Val([field] & ""), "00000000");

 

[IannWard]

after you copy it will lose its format, then just make query to update the field in the table

UPDATE table SET field = Format(Val([field] & ""), "00000000");

Hi

Thanks for your response. I'm still learning and am not quite sure where to put the above to run the update. Instructions on where to put it would be really helpful. I have tried a couple of places but to no avail. I assume I copy as is but replace [field] with my field name?

Many Thanks

 

[arnelgp]

you need to create an update query with something similar.

 

[Cronk]

Going back to basics, I consider it bad design to store numeric data in a text field. Use forms and reports to show leading zero's or separating commas if you need to.

In certain cases where there are leading zero's (phone numbers, postal codes), they are clearly not numeric because you'd never want to perform numeric operations on such data; then use text fields to hold leading zeros.

 

[MarkK]

Totally agree with Cronk, do not store formatted or calculated data. Store your data raw and in its simplest form, which is numeric if possible. If you need formatting done, or calculating, do it at retrieval time, not when you save it.

 

[greenbergmethew]

UPDATE tblindiantime
SET tblindiantime.CountryName = contacts.BusinessCountry
FROM tblindiantime
JOIN contacts
ON -- join condition here
You need a join condition to specify which row should be updated.

If the target table is currently empty then you should use an INSERT instead:

INSERT INTO tblindiantime (CountryName)
SELECT BusinessCountry FROM contacts

goo.gl/SdBrnj