We help IT Professionals succeed at work.

Sort bounded Access Dropdown box

Medium Priority
311 Views
Last Modified: 2012-05-12
Dear Experts,

I have a dropdown on my Access form (frmStudent)

Row Source:

SELECT tblTeachers.tblTeachersID, tblTeachers.tblTeachersName FROM tblTeachers ORDER BY tblTeachers.tblTeachersName, tblTeachers.[tblTeachersName];

The concern is this:

The Teachers names go like:

Mr. Smith
Mrs. Johnson
Ms. Sadowsky
Mr. Henry
Mrs. Jamison

etc.

As you can see when names are entered like this I am unable to sort on the first letter. Is there any way I can modify the query to sort the first letter after (period - space) [. ] ?

...or would there be a better way to do this. The drop down view must look like the names above.

Thanks!

Kind of need this in a hurry so I am offering max points.

Mike
Comment
Watch Question

I may suggest to have fields like:
LastName
FirstName
Prefix

Then you can concatenate them in a query as:
TeacherName:[Prefix] & " " & [FirstName] & " " &[LastName]

In LastName field in query grid view, select Ascending in Sorting row.

Sincerely,
Ed
CERTIFIED EXPERT
Commented:
You can use
ORDER BY trim(mid(tblTeachers.tblTeachersName,instr(tblTeachers.tblTeachersName,".")+1)) ....
but approach with separate fields seems preferrable

Author

Commented:
Awesome! This is exactly what I needed.

Only last names are used so I did not want to separate out fields. I did not think about using the mid function in the query but I see how that works.

Thanks again!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.