By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Appending text to number returns #Error

P: n/a
I have a routine that imports text data to an 'import table'. I'd like
to then clean it up (get rid of all blanks, etc) and then append it to
a data table. The data imports as text, but in the destination I need
it as a number. I've tried using an append query, with Expr1:
CInt([Data]) as the source, but this only results in #Error. Is there
another function I should be using? Will I have to do this in VB code?

Thanks, maddman

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Maddman,
I'd look at the source data. That sort of error appears when somewhere in
the data there is something that won't cleanly convert to a number. I'd
look for data entry clerk sillyness like $3.00 Adj. to PO4050 where the
clerk put a note in with the amount or some such. If something like this is
the case you I usually provide two columns in the result. The first column
is a text column that preserves the data as recieved and the second column
stores it as converted so I have an audit trail in case some manager later
wants to argue with me that my conversion was incorrect.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Maddman" <ma********@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
I have a routine that imports text data to an 'import table'. I'd like
to then clean it up (get rid of all blanks, etc) and then append it to
a data table. The data imports as text, but in the destination I need
it as a number. I've tried using an append query, with Expr1:
CInt([Data]) as the source, but this only results in #Error. Is there
another function I should be using? Will I have to do this in VB code?

Thanks, maddman

Nov 13 '05 #2

P: n/a
Thanks for the quick reply Alan. I did as you suggested, but it didn't
help. The data all looked to be in good shape, just 9 digit numbers.
Also, it returned #Error on every single record. Any other ideas?

Nov 13 '05 #3

P: n/a
You can't store a 9-digit number in an Integer field. You need a Long
Integer field. Max size for Integer is 32768 or so, which is far below
what you need.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.