Convert Field to four digits

[maindaco via AccessMonster.com]

I'm dealing with a problem and need the easiest solution as possible. I have
several tables which have one field in common, let's call it ProductCode,
which is set as "Text" since it use alphanumeric data. The data for the
tables is obtained and pasted from Excel, but, when a code starts wiht "0"
(zero), the code is shown as 3 digits (0473 is shown as 473).

What I need is to have the four digits displayed in the table (0 inclusive),
this is because I use the ProductCode to get information from other tables
and the other tables always shows the 4 digits, so when I try to find one
code with 3 digit only, information is not loaded.

By now, what I've used is several (15 for each table) Update Queries to add
"0" at the beginning to each possible ProductCode with 3 digits.

Is there an easy way to deal with that in one single step or less steps as
possible? Your suggestions are welcome.

 

[Sharkbyte]

Not sure of any easy way, after-the-fact, however you may want to look at
handling the issue from Excel, which is where I believe you are losing your
'0'.

Excel defaults to the General field data type, which will drop a leading '0'
in a field only displaying numeric information. If the column is formatted
to Text, the '0' will remain.

Good luck.

Sharkbyte

 

[Tim Ferguson]

What I need is to have the four digits displayed in the table (0
inclusive), this is because I use the ProductCode to get information
from other tables and the other tables always shows the 4 digits, so
when I try to find one code with 3 digit only, information is not
loaded.

UPDATE MyTable SET MyAlphanumericField = "0" & MyAlphanumericField
WHERE MyAlphanumericField LIKE "[0-9][0-9][0-9]"

The data for the tables is obtained and pasted from Excel, but, when a
code starts wiht "0" (zero), the code is shown as 3 digits (0473 is
shown as 473).

In the future, you probably need to control your Excel file much better.
Can you use an import specification file to force the field to a Text
(somewhere it's being treated as a number)? Can you edit the Excel file
to make the values '0471 (note the single quote at the beginning) to
force a text value? Etc?

Hope that helps

Tim F

 

[KARL DEWEY]

Rather than paste the data from Excel why not import into predefined table
structure?