Alter column conversion problem

[Frank frank]

Hello everybody,
I have an Access table with some fields set as text and I would like
to convert them to integer.

For some reason some values have wrongly been entered with chars, thus
ALTER TABLE my_table ALTER COLUMN my_field Integer
gives me a nice
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

If I manually convert the field using Access, I get warned of possible
data loss, but the conversion takes place anyway.

How can I force the conversion using just SQL? Is there something like
an option "override errors" / "force conversion"?

Thanks
Frank

 

[Bob Barrows [MVP]]

Hello everybody,
I have an Access table with some fields set as text and I would like
to convert them to integer.

For some reason some values have wrongly been entered with chars, thus
ALTER TABLE my_table ALTER COLUMN my_field Integer
gives me a nice
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.

This error message does not seem to have anything to do with your data
in the table. However, having never worked with JetSQL DDL, I may be
wrong.

A quick look at the documentation seems to indicate that you are out of
luck. My suggestion would be to

add a new column with the correct datatype
alter table my_table add column tmpcol integer NULL

use a SQL update statement to explicitly convert the values and put them
into the new column:
update my_table set tmpcol=clng(my_field) where isnumeric(my_field)

if that statement raises an error (isnumeric is not perfect), you will
need to open a recordset and loop through it, setting the new column's
value one record at a time and catching any errors that occur

drop the existing column
ALTER TABLE my_table DROP COLUMN my_field

Add it back in with the correct datatype
alter table my_table add column my_field integer NULL

use a SQL update statement to set its value:
update my_table set tmpcol=clng(my_field)

Drop the temp column
ALTER TABLE my_table DROP COLUMN tmpcol