Value in Query Returning As String and not a Number As Request

[CarlRostron]

I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .

Here is the query

SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker], tblTempBullsWeek3.SymbolCode AS [Long Ticker], tblTempBearsWeek3.[Week 3 Date], tblTempBearsWeek3.Short, tblTempBullsWeek3.Long, tblTempBearsWeek3.[Short Week 3 Close], tblTempBullsWeek3.[Long Week 3 Close], Format((([Long Week 3 Close]/[Short Week 3 Close])-([Long Week 2 Close]/[Short Week 2 Close]))/([Long Week 2 Close]/[Short Week 2 Close])*100,"Fixed") AS [B][% Change][/B], Format([Long Week 2 Close]/[Short Week 2 Close],"Fixed") AS [Start Ratio], Format([Long Week 3 Close]/[Short Week 3 Close],"Fixed") AS [End Ratio], tblTempBearsWeek3.[Short Week 2 Close], tblTempBullsWeek3.[Long Week 2 Close], tblTempBullsWeek3.[Week 2 Date]
FROM tblTempBullsWeek3 INNER JOIN tblTempBearsWeek3 ON tblTempBullsWeek3.[Date Added] = tblTempBearsWeek3.[Date Added];

The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.

Please can you help me resolve this issue.

Thanks

 

[jdraw]

Format returns a string
Try using a Cdbl(....) as below

SELECT tblTempBearsWeek3.SymbolCode AS [Short Ticker]
, tblTempBullsWeek3.SymbolCode AS [Long Ticker]
, tblTempBearsWeek3.[Week 3 Date]
, tblTempBearsWeek3.Short
, tblTempBullsWeek3.Long
, tblTempBearsWeek3.[Short Week 3 Close]
, tblTempBullsWeek3.[Long Week 3 Close]
, cdbl(Format((([Long Week 3 Close]/[Short Week 3 Close])-([Long Week 2 Close]/[Short Week 2 Close]))/([Long Week 2 Close]/[Short Week 2 Close])*100,"Fixed")) AS [% Change]
, cdbl(Format([Long Week 2 Close]/[Short Week 2 Close],"Fixed") AS [Start Ratio], Format([Long Week 3 Close]/[Short Week 3 Close],"Fixed")) AS [End Ratio]
, tblTempBearsWeek3.[Short Week 2 Close]
, tblTempBullsWeek3.[Long Week 2 Close]
, tblTempBullsWeek3.[Week 2 Date]
FROM tblTempBullsWeek3 INNER JOIN tblTempBearsWeek3 ON tblTempBullsWeek3.[Date Added] = tblTempBearsWeek3.[Date Added];

 

[CarlRostron]

Works like a charm!!

Thanks