Convert Text Column to Number

[jiblankman]

I have a datatable with a column of numeric data. The column was originally assigned a type "number" but was converted to "text" by a user.

I need to change back to "number" but I get an error message that says:

Microsoft Access encountered errors while converting the data. The contents of field in 443 record(s) were deleted. Do you want to proceed anyway?

I tried adding a new field and running an update query which assigns the value of the original column to the new column using the CInt, Cdbl and Val functions. I get the same result. Some of the values (443 exactly) are not convereted. As these were originally numbers, I do not understand what is happening.

Unfortunately, this is messing up a number of reports and queries which I need to be able to run. Can anyone help me correct this issue? Thanks.

 

[boblarson]

Those 443 values probably include spaces and you can't add spaces to a number column.

 

[midmented]

If there are spaces involved, I think I'd use something like Cint(Trim([FieldName]).

Just something off the top of my head.
Maybe someone used and O's instead of a zeros?

 

[jiblankman]

As far as I can tell, none of the data has any spaces or O's (letter).

I tried exporting the table to and excel document and creating a new column of numbers, but cannot designate these as numbers when importing the table back into access.

 

[MSAccessRookie]

I have a datatable with a column of numeric data. The column was originally assigned a type "number" but was converted to "text" by a user.

I need to change back to "number" but I get an error message that says:

I tried adding a new field and running an update query which assigns the value of the original column to the new column using the CInt, Cdbl and Val functions. I get the same result. Some of the values (443 exactly) are not convereted. As these were originally numbers, I do not understand what is happening.

Unfortunately, this is messing up a number of reports and queries which I need to be able to run. Can anyone help me correct this issue? Thanks.

I have a datatable with a column of numeric data. The column was originally assigned a type "number" but was converted to "text" by a user.

I need to change back to "number" but I get an error message that says:

I tried adding a new field and running an update query which assigns the value of the original column to the new column using the CInt, Cdbl and Val functions. I get the same result. Some of the values (443 exactly) are not convereted. As these were originally numbers, I do not understand what is happening.

Unfortunately, this is messing up a number of reports and queries which I need to be able to run. Can anyone help me correct this issue? Thanks.

Without seeing the data it is difficult to assist you here. Try the following steps, and report back with the results:

1. Make a COPY of your database.
2. In the DESIGN VIEW of the database copy, Add a new column (type Text) to use for making a copy of the column that has the problem.
3. Copy the values from the OLD COLUMN into the NEW COLUMN.
4. Convert the NEW COLUMN only.
5. Compare the OLD COLUMN to the NEW COLUMN. The error values should have a NULL In the NEW COLUMN

 

[jiblankman]

The same result occurs. The fields that did not convert are left blank.

 

[boblarson]

The same result occurs. The fields that did not convert are left blank.

I would say that the next thing to do is to post your database (or at least one with a copy of the table with the applicable fields in it).

 

[jiblankman]

Here is a copy of the database table with all other information removed for privacy reasons.

 

[Mike375]

When you change the field type to Number then go to the bottom of table design page and select Double instead of the Integer default.

 

[jiblankman]

Thank you. I feel a little stupid that I didn't notice that.