Convert Text to Number in MS Query

[Nighabi]

I have a pivot table that I run against a saved query. The problem is one of the numbers I want to add is s String in the data source so I need to convert it to value.
I tried to use CDBL in the SQL editor of MS Query but when I format it as below
Select CDBL(DataSource.TransQty),
DataSource.Product
From DataSource
I get an error
Lexical element not found: FROM
Not sure what I am doing wrong.

 

[jvanbonn]

Just a thought...

Could you import the number as a string and multiply it by 1 in excel to make it a number?

 

[Nighabi]

Problem with that is I'm trying to bring the data directly into a pivot table to avoid having to refresh the data and then refreshing the pivot table.

 

[jvanbonn]

How about creating a Calculated Field in your pivot table that is the product of your text field?

 

[Nighabi]

That doesn't work because one of the fields isn't a number. I guess pivot tables work differently than when you just retrieve the data. When I dump the data to a table, I can do math on the field and it works, if I bring it directly into a pivot table, I can't do math because it contains strings.