Data Type Conversion Error 1

[SarasotaIT]

I had data in a char. field that was in the form 0000-000-0000. After stripping out the dashes to make the data all numeric, I tried to convert the field from char. to long integer. Access errors out (table contains 280,000 records). SO, I thought I would outsmart Access and create an exact copy of the table w/o data, change the datatype of the field in the new empty table, and then run an Insert INTO query to import the records into the new table. Query runs but Access reports that 8000 records will be converted to Null due to data type conversion errors. I am sure all data in field is numeric with no spaces. I even tried exporting table to .txt (w/o "&quot and importing but errors prevent that field's data from importing. Any suggestions?

Thanks in advance!

 

[dhookom]

Long integer will store numbers up to 2,147,483,648. You might want to use Decimal. Is there a reason why you are switching from text to numeric? Are you planning on doing calculations with these values?

Duane
MS Access MVP

 

[SarasotaIT]

Thanks!
no calculations...merely to keep from having to write and execute queries that trim the leading zeros. I know converting to integer will drop leading zeros, otherwise I have to do something like the following:

UPDATE [tablename] SET [tablename].colname = Right([colname],9); and so on...

The amount of leading 0's varies thru all 280,000 records. Just trying to avoid the extra work. Whatever you can offer would be much appreciated!
Thanks again!
Terry

 

[dhookom]

I would write a small function that removes the leading 0s and then run an update query.

Function TrimZeros(pstrText As String) As String
Dim strChar As String
strChar = Left(pstrText, 1)
Do Until strChar <> &quot;0&quot;
pstrText = Mid(pstrText, 2)
strChar = Left(pstrText, 1)
Loop
TrimZeros = pstrText
End Function

Duane
MS Access MVP

 

[SarasotaIT]

Thanks, Duane!

I am not much of a programmer...if it falls out of the scope of basic SQL, I am lost. How would I assign your Fn to a particular table? Is this done under Modules? I would appreciate any tips on how to apply it. :-/
Thanks yet again!
Terry

 

[dhookom]

Create a new blank module and paste the code into it. Save the module as &quot;basCalcs&quot;. Then press Ctrl+G to open the immediate/debug window. Enter:
+-------------------
|? TrimZeros(&quot;0001234&quot
|
and press enter. This should test your function.
Then create an update query where your field is updated:
UPDATE [tablename] SET [tablename].colname = TrimZeros([colname]);

PLEASE backup your mdb or table prior to running any update queries.

Duane
MS Access MVP

 

[SarasotaIT]

IT WORKED!! HATS OFF TO YOU! KUDOS, DUANE!!! I know this was a small step for Access gods like yourself, but it was huge to me. Thanks much!!!
Terry

 

[dhookom]

Writing code and your own functions is fairly easy once you get into it a little. Do try to add comments and error handling which I generally leave out when posting.

Duane
MS Access MVP