Can't SELECT DISTINCT and the Nz Function work Together? 1

[Mike555]

I have a query using a SELECT DISTINCT statement and an ORDER BY clause containing an Nz Function, similar to the following...

SELECT DISTINCT *
FROM TableName
ORDER BY Nz(TableName.Column, "No Data");

The problem is that I can't run this query using the Nz Function as it currently exists. Upon running the query I get the error: "ORDER BY clause (Nz(TableName.Column, "No Data")) conflicts with DISTINCT." If I remove the Nz function the statement works perfectly. Is there a way to run this query using both the DISTINCT statement and the Nz function? Thanks!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[lespaul]

does this work and do what you want:

SELECT DISTINCT TableName.*, Nz(TableName.Column, "No Data")
FROM TableName
ORDER BY Nz(TableName.Column, "No Data");

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Often the need of the DISTINCT predicate indicates a poor database design ...
Why do you need SELECT DISTINCT * ? TableName has no PrimaryKey ? You have WHOLE record duplication ?

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

 

[Mike555]

Thanks lespaul, that solves it!
PHV, For the purposes of posting the question I didn't use my ACTUAL syntax from the query, and the actual query does not select *. Thanks.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.