Change Text format to Numbers format in a query

[PerryK]

I have a Cross Tab Query that uses a linked TXT file for data.
The file contains mostly numbers, but they are formated as Text.
The file comes from a server, and I do not have the ability to change the
format.

Is there a way to change the format in the Crosstab Query so that it is
formated as a number instead of TXT?

This is the SQL of the Query:

TRANSFORM Max([RM MIS From L01A Rpts].OGM_CALLS_PCT_PLAN) AS
MaxOfOGM_CALLS_PCT_PLAN
SELECT [RM MIS From L01A Rpts].ORG_GROUP_ID AS Dist
FROM [RM MIS From L01A Rpts]
WHERE ((([RM MIS From L01A Rpts].DISPLAY_HEADER) Not Like "2008*"))
GROUP BY [RM MIS From L01A Rpts].ORG_GROUP_ID
PIVOT [RM MIS From L01A Rpts].DISPLAY_HEADER In
('200901','200902','200903','200904','200905','200906','200907','200908','200909','200910','200911','200912','3 Month','YTD');

 

[Dale Fye]

I assume the value you want convert to numeric is OGM_Calls_pct_plan. Try
using the Val( ) function to convert the value before using MAX.

TRANSFORM Max(Val([RM MIS From L01A Rpts].OGM_CALLS_PCT_PLAN)) AS
MaxOfOGM_CALLS_PCT_PLAN

 

[Jerry Whittle]

Use the CDbl or Val functions on the text field in question. Note: Both of
those functions have problems with null fields so you need to ensure that
there aren't any or use something like the NZ function to 'fix' the nulls.