Problem with zeros when importing numbers as text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem when I import numbers from a excel data sheet. I have
numbers that I import and need them strictly as a text value. I have all
references to that field in Access as data type text. In the Excel sheet I
have the column formatted as text. The problem is when I import the
numbers(text) with no zero's it is fine. However when I import something like
02 it imports as 2. 01 is 1, 04 imports a 4 and so on. Any suggestions.

Thanks,

Chris
 
Update.
Actually the problem is more detailed.When I import the text value 0 (zero)
it works,but if I import 01 OR 02. It is blank field and gives a import
error. Before it was just dropping the zero like I explained earlier. Now it
is leaving it blank. Confused?
Thanks Again

Chris
 
Hi Chris,

There are basically two ways to go. One is to modify the values in the
Excel sheet to force them to be imported as text: do this by prefixing
each with an apostrophe, e.g. 02 becomes '02. The apostrophes show up in
the Excel formula bar but almost nowhere else, and are not imported into
Acccess, but they do ensure that both Excel and Access treat the values
as strings of characters and not as numbers.

The other is to import as now, so "02" becomes "2". Then use an update
query to restore the leading zeroes, something like
UPDATE MyTable
SET TextField = Format(CLng([TextField],"00"))
WHERE TextField IS NOT NULL
;
 
Back
Top