asked on

ORDER BY [--] Conflicts with DISTINCT

I have a VB6 program with an ADO connection to Access97 database. A query that I set up in Access (and ran successfully) was copied/pasted to my VB program. However, when I try to open a recordset based on the SQL I get the error: ORDER BY [fld] Conflicts with DISTINCT.

The SQL:

strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Name, "
strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID = tblPlacementInfo.IDLink) "
strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
strSelect = strSelect & "ORDER BY [LastName], [FirstName], [MiddleName] ;"

I suspect someone's encountered this somewhere else, but I did not find any refs.

Thanks.

Ken

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andyclap

Unfortunately this is the way it works in access. If you use an orderby after a distinct you'll need to select the fields you're ordering by too. You can use a calclated field, but annoyingly you need to use the calculation again rather than the alias to reference it in the order by

You can either use:

strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]
AS Name, "
strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID =
tblPlacementInfo.IDLink) "
strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
'order by the calculated field
strSelect = strSelect & "ORDER BY [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] ;"

or

strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]
AS Name, "
strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
'have to bring back these fields too
strSelect = strSelect & ", [LastName], [FirstName], [MiddleName] "
strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID =
tblPlacementInfo.IDLink) "
strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
strSelect = strSelect & "ORDER BY [LastName], [FirstName], [MiddleName] ;"

kenspencer

ASKER

Oops! I had copied/pasted the SQL, then edited it to remove the double-quotes from the calc field (Name), and that's where I took out the ", " and " " entirely. I did not realize that DISTINCT causes problems with ORDER BY. Tim was first and so gets the points. Thanks to Andy for the explanation.

Ken