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.
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.