Alphabetizing - with an apostrophe

[jericorbitt]

Hi, I may be dense, but I cannot find an answer to this question anywhere. If it has a simple and obvious answer, my apologies.

I have created an index with a field called "Surname" -- I need to know how to force Access to alphabetize the names disregarding the apostrophes (or a space) - for example, O'Brien. The way it works now, all the O' (s) go at the beginning of the Os (O'Brien, O'Malley, O'Neal, Oakley). I want it to go - Oakley, O'Brien, Oddfellow, O'Malley.

Is there a way to do this and still display the apostrope? I feel that I could set something for the field in the design view, but for the life of me I can't figure it out.

Thanks in advance for any help.
Jeri

 

[Pat Hartman]

You can create a field without spaces or special characters and use that to sort on. There is no need to store this field so create it in the query.