union query causes DISTINCT not to work?

[davikokar]

Hallo,
I'm using an unbound combo box in order to set search criteria. The combo box source is this:

SELECT DISTINCT tbl_Person.Per_ID, tbl_Person.Per_Name FROM tbl_Person UNION SELECT "*", "<All>" FROM tbl_Person;

the combobox shows only the second column (the one with ID is hidden)

The problem is that the distinct doesn't work...if I pop down the combobox I can go through all the single listed values. And worst of it if I write in it a name that appears more then once the search query will return me only the first value record it finds. The search query is something like that:

SELECT [tbl_Person].[Per_Name], [tbl_Person].[Per_LastName], [tbl_Type].[Typ_ID], [tbl_Country].[Cou_ID], [tbl_Person].[Per_ID]

FROM tbl_Country RIGHT JOIN (tbl_Type RIGHT JOIN tbl_Person ON [tbl_Type].[Typ_ID]=[tbl_Person].[Per_Type]) ON [tbl_Country].[Cou_ID]=[tbl_Person].[Per_Country]


WHERE

((ISNULL([tbl_Type].[typ_ID]) And [Forms]![Form1]![cbo_type]="*") Or [tbl_Type].[typ_ID] Like [Forms]![Form1]![cbo_type])

And

((ISNULL([tbl_Country].[Cou_ID]) And [Forms]![Form1]![cbo_country]="*") Or [tbl_Country].[cou_ID] Like [Forms]![Form1]![cbo_country])

And

((ISNULL([tbl_Person].[per_ID]) And [Forms]![Form1]![cbo_person]) Or [tbl_Person].[per_ID] Like [Forms]![Form1]![cbo_person]);


Does someone have a clue?

 

[Crowley16]

that's probably because you have the same name for different id's...

the distinct clause returns distince records, not fields...

if you just select distinct name, then you'll get distinct names, same with id...

 

[davikokar]

you're right... I didn't noticed that. thanks