Sort bounded Access Dropdown box
Dear Experts,
I have a dropdown on my Access form (frmStudent)
Row Source:
SELECT tblTeachers.tblTeachersID,
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
I have a dropdown on my Access form (frmStudent)
Row Source:
SELECT tblTeachers.tblTeachersID,
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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!
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!
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