Converting varchar to numeric

[Guest]

I have a linked table where the columns are datatype of varchar. One of the
columns is a dollar amount and i need to sum it in a query that I have built
against that table. It wont allow me to sum a varchar so I need to convert
the value to a numeric. I developed the query in the SQL Design mode and
tried using the either the sql convert and cast functions but access does not
recognize those functions.

Can anyone tell me what function i can use to convert this to a numeric data
type?

Thanks.

 

[Douglas J. Steele]

CCur.

While your data may be stored in SQL Server, when you're dealing with a
linked table, you need to use Access (and VBA) functions, not T-SQL
functions.

 

[Guest]

Thanks. CCur worked but when I put 'Sum(CCur(expression)) i get the
following error

ODBC -- Call Failed.

Error converting data type varchar to float. (#8114)

It still thinks its a varchar.

 

[i_takeuti]

I have a linked table where the columns are datatype of varchar. One of
the
columns is a dollar amount and i need to sum it in a query that I have
built
against that table. It wont allow me to sum a varchar so I need to
convert
the value to a numeric. I developed the query in the SQL Design mode and
tried using the either the sql convert and cast functions but access does
not
recognize those functions.

Can anyone tell me what function i can use to convert this to a numeric
data
type?

Thanks.

 

[Douglas J. Steele]

Are you sure that every single entry contains only a numeric value? If one
(or more) contains non-numeric values, you'll definitely get that error.