Alter column conversion problem

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

Re: Alter column conversion problem

Frank frank@null.null wrote:
> 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


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.