Drop down list in Access

[AJHY]

Hi

Help plz, I have a combo box with names in: -

E.G

DR A Bloggs
DR A B Smith
DR A B C Another
DR B F Able
DR F S Norman

What I’m wanting to achieve is to sort the list by Surname ignoring the Title, and other forename characters, but still want to display them.

Not sure which is the best way to do this e.g VB???

P.S I am developing the database in MS Access 2002

Thanks in advance!!

Andy

[Scott Price]

If your table is designed as it should be (i.e. with name data separated into a First Name, Last Name and Title field) you will have no problem.
[CODE=sql]
SELECT [Title] & " " & [FirstName] & " " & [LastName] AS Doctor
FROM TableDoctor
ORDER BY TableDoctor.Las tName;
[/CODE]

Should do the trick.

Regards,
Scott

[AJHY]

Hi

Thanks for the quick responce, ah well the database I am currently working on I have inherited from another designer.

All the names are contained within the same field. Therefore I have a problem.

Thanks

Andy

[Scott Price]

Hi Andy,

My suggestion is to restructure your table according to accepted database design principles before going any further.

And no, it's not acceptable to not re-structure just because someone else created the design in the first place.

VBA is remarkably flexible, and it is possible to split the name and sort it as you wish. However, this is NEVER acceptable in my opinion! Using VBA to compensate for bad design is a bankrupt policy.

It's a simple matter to add two columns to your existing table. It breaks nothing, and fixes your problem. You might feel a bit daunted at thinking of re-entering the existing data, and possibly restructuring the queries that enter the data in the first place, but, TRUST ME... you will find it far easier in the long run to do it right than continue attempting to fight with an improperly designed database.

Regards,
Scott

[NeoPa]

Acceptable or not, there's really no practicable way to sort by a surname when that surname is not the start (or even n chars in) of a field.
The suggestion to separate the data into individual fields seems to be your best (only) choice.