Solved Struggling with Distinct

[KitaYama]

I have the following query (qryMaterials)

SELECT
    p.DrawingNo,
    o.RecID
    .....
    TempVars!UsedMaterial_SetCount * o.Quantity * u.UsedQuantity AS Quan,
    .....
FROM
    (tblOrders AS o
    INNER JOIN tblProducts AS p ON o.OrderProductFK = p.ProductPK)
    INNER JOIN tblMaterials AS u ON p.ProductPK = u.UsedProductFK
WHERE
    u.LaNc="NC"
ORDER BY
    u.ProgramNo;

This has no problem.
I need another sql to be used as a rowsoruce of a combo box:

SELECT DISTINCT
    DrawingNo
FROM
    qryMaterials
WHERE
    RecID IN ('S1024120001','S1024120002','S1024120003','S1024120004');

This one throws ODBC-- call failed.

I tried to use a subquery :

SELECT DISTINCT
    DrawingNo
FROM
    (SELECT DrawingNo FROM qryMaterials) AS SubQ
WHERE
    RecID IN ('S1024120001','S1024120002','S1024120003','S1024120004');

Still ODBC error.

Using an aggregate query works:

SELECT
    DrawingNo
FROM
    qryMaterials
WHERE
    RecID IN ('S1024120001','S1024120002','S1024120003','S1024120004')
GROUP BY
    DrawingNo;

Is there any explanation on why I can not use Distinct?

thanks.

 

[KitaYama]

Should have said:
Error message has no number.
BE is sql server.

Thanks again.

 

[plog]

Gotta ask, is the WHERE clause actually just periods? If not, can you just show us the actual code of it? If so, that's an issue.

 

[KitaYama]

Gotta ask, is the WHERE clause actually just periods? If not, can you just show us the actual code of it? If so, that's an issue.

I omitted them to simplify the sql.
Added to my main post above, but I don't think it has any effect.

Thanks.

 

[plog]

Is RecId in the SELECT of qryMaterials?

 

[KitaYama]

Is RecId in the SELECT of qryMaterials?

Yes. I edited my post twice. You may have missed the second edit.

Thanks again.

 

[KitaYama]

OK. I found the culprit.
I assumed the tempvar is the problem so I removed the tempvar from qryMaterial. It's now:
o.Quantity * u.UsedQuantity AS Quan,

After this change, running Distinct query throws a different error:

I put back the tempvar and added OrderBy to Distinct.
After this change, it seems I have no problem. Distinct shows the result.
No Error.
This works:

SELECT DISTINCT
    DrawingNo
FROM
    qryMaterials
WHERE
    .....
ORDER BY
    DrawingNo;

I never knew Distinct needs Order BY.

And I wish Access could show the error even when the tempVar is included in qryMaterials.
I had to pull it out to make Access show the correct error message.

Thanks for your time.