I'm having a DISTINCT problem! :)

[bujin]

Hi. Got a little problem with an Access database application I'm writing. I am using VBA to create a query string. The query selects all trainees from the list who have the same field officer (by a field officer code). The list is then narrowed down to show all surveys completed by the trainee between two dates input on a form. The trainee ID's that fit the criteria are stored in a temporary table. I then want to load a listbox on a form with the trainee ID from the temp table, along with the corresponding trainee record from the trainee table (to show only the trainee records for the Field Officer that were completed in this time period). Now, the problem is that because a trainee can complete more than one (different) survey in the time period, his/her ID can be loaded into the temp table more than once. I only want to display it once. I have tried using DISTINCT in the VB query string, but this then cuts out ALL records (for some reason!). I've tried using DISTINCTROW, and this just displays all records. I've manually created a query using the same tables, and added DISTINCT before the Trainee ID, and it works fine - i.e. the query works fine, but it won't work for the listbox. Anyone know what I'm doing wrong?

Thx

 

[bujin]

S'okay - I got it working now. FYI it wasn't working because I had an ORDER BY in the query string, and DISTINCT obviously doesn't like working with ORDER BY.

As a foot-note, I appologise for doing this again (posting a message and then almost immediately posting the answer, that is), it's just that each time I post a message, I've just spent the last few hours trying to get around a problem, but as soon as I post a message to ask for advice, I'll think of something that can be done, which 9 times out of 10 works!

 

[Mats]

...done the same myself a couple of times, writing down the problem makes you think it through - and that usually open your eyes ;-)