Field options not alphabeticising

[Juliaataccess]

Hi Access experts,

I have been using Access for many years, but have come against a problem which I can't fathom.

I have a database with a field for "Organisation Type" which contains all the various options which can be chosen by the user.

However, when I'm in a field, it refuses to show that list in alphabetical order, defaulting to an order I don't recognise which is most unhelpful as it makes it almost impossible to find the right item, especially for a novice user.

I thought this was perhaps because the form was based on the database table, and this didn't allow sorting.

So, to test my theory, I created a query based on the table and added alphabetical sorting to that field. As soon as I use the query as a basis for the field, I find the problem remains.

I would appreciate your assistance in this matter.

Regards,
Julia Evans

 

[gemma-the-husky]

i assume you have a list box, or combo box. you need to base the control on a QUERY, which will sort the date into the required order.

there is no intrinsic order with a table. access just gets the rows in the most efficient way it can which is no doubt the source of your problem.

 

[Galaxiom]

As Dave indicated, a query is essential to order the records. I am surprised you would not have encountered this reality in many years of using Access.

But you say you are using a query with an Order By clause. It is probably something so obvious you will kick yourself when you take a break and look again.

 

[Juliaataccess]

Hi,

Thanks for your replies.

There have been long gaps in my Access experience over the years. At one point I used it daily, now there are weeks or even months between my use.

Even when I based the field on a Query with an Order By command, it still failed to alphbeticise !

I decided to change the field type to one based on a table with the relevant entries, which keeps the A-Z order I need. It allows me to select multiple values, which I realised I needed.
Regards,
Julia

 

[Galaxiom]

Sounds like you are using a multivalue field.

Best avoided as they are an Access only feature that will prevent any future upsizing to a other database systems. They are also far more complex to work with in VBA and queries.