Convert varchar to int

[TDC21]

I am creating a table view in SQL as the source data for a form I am trying to build but I am having a issue with a data type conversion. I know this part of the problem inst directly related to access but I am hoping it might be close enough to pass. I would prefer the source data be complete then to fix it outside of SQL

Our parts table holds both length and width as numeric values however there is no field for thickness. This was added to a user defined field, the field is validated, it only has numeric entries in a xxx.xxxx format but the data type is still varchar (80). I am trying to build some functionality to calculate weight but I need this field as a numeric value.

I have tried the following expressions but no luck,

CAST(dbo.PART.USER_1 AS NUMERIC(5, 4)) AS THICK

CAST(CAST(dbo.PART.USER_1 AS NUMERIC(5, 4)) AS INT) AS THICK

The error message I get is "Error converting data type to varchar to numeric"

If anyone has a sugestion it is much appreciated

 

[james_lankford]

so its stored in the database as text as this ?
1234567

but the form displays it as this ?
123.4567

 

[TDC21]

Visually it looked fine, it was still stored as 123.4567 in the database, just in a text format. The issue was trying to run any mathematical calculations of the field. I did finally come up with a STMT that recast the value as numeric, and avoided multiplying by 0 errors. If it will help anyone else, see bellow.

CAST(CASE WHEN ISNUMERIC(isnull(dbo.PART.user_1, '0')) = 0 THEN '0' ELSE isnull(dbo.PART.user_1, '0') END AS NUMERIC(18, 4)) AS THICKNESS