Error 3093 Question

[bwion]

I have a query for the rowsource of a combobox:

SELECT DISTINCT tblAircraft.DN FROM tblAircraft WHERE
(((tblAircraft.Program)=[cmbProgram])) ORDER BY Val(tblAircraft.DN) DESC;

and when I run this query I get the following error:

ORDER BY clause Val(tblAircraft.DN) conflicts with DISTINCT. (Error 3093)

I have the DISTINCT because the same DN is used for multiple records in
tblAircraft. And I use the "Val()" function because the DN's are text
datatype and I want them to sort in order like they are numbers, not text.

Any suggestions?

Ben

 

[John W. Vinson/MVP]

I have the DISTINCT because the same DN is used for multiple records in
tblAircraft. And I use the "Val()" function because the DN's are text
datatype and I want them to sort in order like they are numbers, not text.

Any suggestions?

Just be consistant: you're using Val() in one place and not the other.
Try

SELECT DISTINCT Val(tblAircraft.DN) FROM tblAircraft WHERE
(((tblAircraft.Program)=[cmbProgram])) ORDER BY Val(tblAircraft.DN)
DESC;