I am trying to help an individual with a MS Query.

In Excel 97 MS Query the script was written:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR

In Excel XP I can run an existing Query and it returns the data as
expected, but when I view the query it is:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'

However if I try to create a new query using XP MS Query then the AS
YEAR or AS 'YEAR' will be removed by MS Query and my column header will
now be:

TO_CHAR(LABTRANS.STARTDATE, 'YYYY')

Here are my examples:

MS Excel 97 Query

SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS,
LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE,
VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME
= 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE
= 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND
LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE =
'NON-WORK' ORDER BY LABTRANS.STARTDATE

MS Excel XP Query

SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS,
LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE,
VALUELIST.VALDESC,
TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME
= 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE
= 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND
LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE =
'NON-WORK' ORDER BY LABTRANS.STARTDATE

In Excel XP, no matter what I put in the AS part of the statement when
I execute the script it runs and removes the AS part from the
statement. This only happens when I run the script from MS Query, if I
stay in Excel and just refresh the data, then it works fine.

I did not have this problem in Excel 97.

Any suggestions?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/