Change Text format to Numbers format in a query

  • Thread starter Thread starter PerryK
  • Start date Start date
P

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');
 
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
 
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.
 
Back
Top