query INSIDE iif???

[m19peters]

Can do it in sql server... but cant seem to get it done in access.
Right now i have something like this...

SELECT TRIM(DISTINCT_ITEMS.ITEM) AS ITEM,
First(HEADER.A_DRAW_NUMBER) AS A_DRAW_NUMBER,
IIf(IsNull(First(HEADER.A_DRAW_TITLE1)),
[SELECT First(ASSEMBLY_PARTS.H_NAME) FROM ASSEMBLY_PARTS
WHERE DISTINCT_ITEMS.ITEM = ASSEMBLY_PARTS.H_PART_NUMBER].,
HEADER.A_DRAW_TITLE1) AS A_DRAW_TITLE1
FROM BIG UNION SUBQUERY...
GROUP BY ITEM.....

Gives me the old missing operator error. Is this possible in access?
If so how? Thanks...

 

[[MVP] S.Clark]

Not really. Subqueries are more limited in Access than in SQL server.

From the help file:
You can use a subquery instead of an expression in the field list of a
SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a
SELECT statement to provide a set of one or more specific values to evaluate
in the WHERE or HAVING clause expression.

 

[Gary Walter]

Can do it in sql server... but cant seem to get it done in access.
Right now i have something like this...

SELECT TRIM(DISTINCT_ITEMS.ITEM) AS ITEM,
First(HEADER.A_DRAW_NUMBER) AS A_DRAW_NUMBER,
IIf(IsNull(First(HEADER.A_DRAW_TITLE1)),
[SELECT First(ASSEMBLY_PARTS.H_NAME) FROM ASSEMBLY_PARTS
WHERE DISTINCT_ITEMS.ITEM = ASSEMBLY_PARTS.H_PART_NUMBER].,
HEADER.A_DRAW_TITLE1) AS A_DRAW_TITLE1
FROM BIG UNION SUBQUERY...
GROUP BY ITEM.....

Gives me the old missing operator error. Is this possible in access?
If so how? Thanks...

SELECT

TRIM(DISTINCT_ITEMS.ITEM) AS ITEM,

First(HEADER.A_DRAW_NUMBER) AS A_DRAW_NUMBER,

IIf(

IsNull(First(HEADER.A_DRAW_TITLE1)),

[SELECT First(ASSEMBLY_PARTS.H_NAME) FROM ASSEMBLY_PARTS
WHERE DISTINCT_ITEMS.ITEM = ASSEMBLY_PARTS.H_PART_NUMBER].,

HEADER.A_DRAW_TITLE1) AS A_DRAW_TITLE1

FROM BIG UNION SUBQUERY...
GROUP BY ITEM.....

you might try changing

[SELECT First(ASSEMBLY_PARTS.H_NAME) FROM ASSEMBLY_PARTS
WHERE DISTINCT_ITEMS.ITEM = ASSEMBLY_PARTS.H_PART_NUMBER].,

to

(SELECT First(A.H_NAME) As F FROM ASSEMBLY_PARTS As A
WHERE A.H_PART_NUMBER=DISTINCT_ITEMS.ITEM),


The following worked for me in a
test db

SELECT
Sales.stQtr,
Sales.stValue,
Sales.stObj,
IIf(True,
(SELECT First(S.stQtr) AS FirstOfstQtr
FROM Sales As S
WHERE S.stQtr=Sales.stQtr),0) AS test
FROM Sales;

Of course this was really simple test
that did not involve "big union query"
what ever that is.

Else, you might use Domain function...

good luck,

gary