Problem with zeros when importing numbers as text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diggsy - 22 Nov 2004 20:33 GMT

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

Reply to this Message

Diggsy - 22 Nov 2004 21:47 GMT

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

> 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
[quoted text clipped - 6 lines]
>
> Chris

Reply to this Message

Jamie Collins - 23 Nov 2004 09:33 GMT

> 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?

No because I'm familiar with the issue. See:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--

Reply to this Message

John Nurick - 23 Nov 2004 05:12 GMT

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
;

>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
[quoted text clipped - 6 lines]
>
>Chris

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message