Multi Valued field sort

[bugman61]

I have a report where I am trying to display the items in a multi valued field sorted by name. The report is based on a "Meetings" table with a multi valued field (Participants) linking to a "People" table. The problem is that I can never get the people sorted by name, it always sorts by the associated ID in the People table. The SQL I have right now is as follows:



SELECT People.ID, People.FirstName+" "+People.LastName AS Expr1
FROM People
ORDER BY People.LastName, People.FirstName;

Any help would be greatly appreciated.

[NTC]

I initially focused on the multi value field type - as this field type is kind of a pain for which alot of developers avoid.......but I noticed in this case perhaps your issue is that the sorting of a report when it is defined in the report property overrides the sorting of the record source. And since you presented the sql of the record source and didn't discuss the sort property of the report - take a look at implementing it there and see if that works for you......

hope it helps.

[bugman61]

Thanks for the help.

The Meetings report is filtered down to a single meeting based on user selection, i.e. the click a button to get the report for meeting 5. The values from the multivalued field are displayed in a text box on the report. I do not have anything in the Order By property for the report since it always shows 1 record. I did try playing around with it, and it had no effect.

Elsewhere on the form for the user to enter the meeting information, I had no trouble at all setting up the order for names in the drop down box for selecting the values for the participants field - it acutally the exact same SQL for the Row Source. I just wish I could figure out how to get the names to display correctly on the report.

When I started this project I hadn't ever used access and only did some minor SQL and CFM programming 10 years ago. I had a sneaking suspicion that using the multivalued field might be a pain down the road but did it anyway.