error converting the data

[wdbouk]

hello,

I have a column full of numbers but in text format. I tried converting the column from text to number but i lost a lot of observations by doing so (error message). I am sure that all of the observations are numbers. it used to be 6 numbers + 3 letters and I updated the column to remove the letter by selecting left(column,6). Can Anyone tell me if their is a way to avoid losing the data through conversion (i need to convert them to export them to matlab which recognizes only number format and not text)
thx

 

[vbkris]

i dont get it, the data is all numeric and still ur unable to convert the column into a numeric type?

i would suggest creating one more column of a numeric type and update the text column's data into this numeric column.

see if there errors are thrown...

Known is handfull, Unknown is worldfull

 

[wdbouk]

I created a new column (number format) and i run an update query where the value in the new colum is equal to the test column . I received the following err:

Microsoft didn't update 19811 field(s) due to a type conversion error. Any other hint

 

[vbkris]

can i have ur query?

Known is handfull, Unknown is worldfull

 

[wdbouk]

Hi It is very simple.

I added a column (Field1) in the table and i made its format a number format then I wrote the update query:
UPDATE All SET Field1=Cusip;
where the table is "All" and the cusip column has a text format

 

[vbkris]

try this:
UPDATE All SET Field1=cast(Cusip as numeric)

Known is handfull, Unknown is worldfull

 

[wdbouk]

there is a syntax error in the UPDATE All SET Field1=cast(Cusip as numeric) expression. i tried many changes without success.

 

[SQLDenis]

ALL is a keyword, try

UPDATE [All] SET Field1....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[wdbouk]

nope. still syntax error

 

[SQLDenis]

what is the numeric precision and scale of the field in the table?

This does not give me a syntax error when I validate this statement

UPDATE [All] SET Field1=cast(Cusip as numeric)

It gives me a 'Invalid object name 'All'.' error

what is the exact error message that you are getting?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Let's stop the ball for a second...

> Microsoft didn't update 19811 field(s) due to a type conversion error.

Microsoft what? Access? :>

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[wdbouk]

sybtax error (missing operator)in query expression 'cast (cusip as numeric)'

 

[SQLBill]

You are using Microsoft SQL Server, correct?

-SQLBill

Posting advice: FAQ481-4875

 

[wdbouk]

yes. Sql microsoft access

 

[SQLDenis]

try this forum forum701

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

yes. Sql microsoft access

Then the answer was NO. I asked if you were using Microsoft's SQL Server. That is a different database from Microsoft Access. SQL Server uses Transact-SQL, Access uses Jet-SQL.

-SQLBill

Posting advice: FAQ481-4875

 

[wdbouk]

sorry for that. i am not very familiar with Access and simply trying to organize the data and export it to matlab

 

[SQLBill]

So, do as SQLDenis suggested and post in Forum701. That's the forum for Microsoft Access.

-SQLBill

Posting advice: FAQ481-4875

 

[wdbouk]

sorry for the inconvinience. i will post the same thread in 701

 

[SQLDenis]

yes or SQLBill will Bill you for the SQL (sorry couldn't resist, it's Friday after all)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/