Strange query behavior

[LHWC]

I have a query with the following SQL statement:

SELECT SUBQ.NEW_TYPE, Sum([QTY]*[QTY1]) AS FQTY
FROM (SELECT IIf(InStr([TYPE],"_")>0,Left([TYPE],InStr([TYPE],"_")-1),[TYPE]) AS NEW_TYPE,QTY,QTY1
FROM [Unit Master]) AS SUBQ
GROUP BY SUBQ.NEW_TYPE;

If I run it and then change to SQL view the code has been changed to:

SELECT SUBQ.NEW_TYPE, Sum([QTY]*[QTY1]) AS FQTY
FROM [SELECT IIf(InStr([TYPE],"_")>0,Left([TYPE],InStr([TYPE],"_")-1),[TYPE]) AS NEW_TYPE,QTY,QTY1
FROM [Unit Master]]. AS SUBQ
GROUP BY SUBQ.NEW_TYPE;

The () have changed to []. in the FROM statement. It still displays the same record set, but I have code in a cmd Click event that uses the REPLACE function to redirect the table source (i.e. Unit Master becomes GrayJob). When I run that code I get a runtime error "improper use of brackets".
Does anyone know why this occurs and how to prevent it?
Thanks in advance.

 

[psemianonymous]

Yeah, the syntax is
SELECT field(s)
FROM table(s)
WHERE criteria


So you should only have one FROM. I think you added the calculated fields manually, but you should have put them BEFORE the FROM clause, not AFTER. So

SELECT SUBQ.NEW_TYPE, Sum([QTY]*[QTY1]) AS FQTY, IIf(InStr([TYPE],"_")>0,Left([TYPE],InStr([TYPE],"_")-1),[TYPE]) AS NEW_TYPE, QTY, QTY1
FROM [Unit Master] AS SUBQ
GROUP BY SUBQ.NEW_TYPE;

But I don't think this is what you mean.

If you actually need to use a subquery, then by all means make a query object and refer to "qrySubqueryName" as the subquery. That's what I do.


If in doubt, refer to the JET SQL reference.

 

[PHV]

foolio12, the syntax
FROM (SELECT instruction) AS alias
is perfectly legal, even for JOIN.
Seems to be a bug of the query designer tool, perhaps because M$ has some difficulties to understand well the ANSI standards.;-)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244