How to get the menu of queries that I have asked to appear in numeric/alpha order

[jennyp29]

I am putting information into a database that someone else has built, it is a very simple database, and is built to put in answers to a questionnaire. So the Queries/Reports that I am building are based on the Question Number, but they are appearing in a strange order i.e. 2 appears after 199. How can I get Access to make the lists appear in numeric order?

[missinglinq]

The Question Numbers (which you want things sorted by) are defined as Text in the underlying table, and this is how text would be sorted.

If you have access to table Design View, you can simply change the datatype of the field to Numbers. IF you don't have access to table design, in a query, you can create a calculated field like this:

SortQuestionNum ber: CInt([QuestionNumber])

replacing

QuestionNumber

with the actual name of your field. Then sort by

SortQuestionNum ber

Ditto in Report Design, sort on the calculated field.

Welcome to Bytes!

Linq ;0)>

[jennyp29]

I have access to table design, the only problem is that the primary key is numeric and text ie: 100a, 100b, 100c, 101a,102a,102b. etc. even when it only has one part it has 'a' attached. Hope this makes sense! Thank you for the welcome, but have been on several occasions when you were the scripts.

[missinglinq]

All right, you'll have to do this in a query, and create two calculated fields, replacing QuestionNumber in the examples below with your actual field name.

Create this field first:

SortQuestionNum ber: Val([QuestionNumber])

Then Create this field:

SortQuestionAlp ha:Right([QuestionNumber],1)

When looking at the fields in the Query Design Grid, Access sorts from left to right, which is why you have to create the calculated fields in the order I gave you. Now, set the Sort of

SortQuestionNum ber: Val([QuestionNumber])

to Ascending and then set sort order of

SortQuestionAlp ha:Right([QuestionNumber],1)

to Ascending. When the query is run or used as the base for a form, the order should now be as you need it. Once again, in Reports you'll have to sort on the calculated fields, SortQuestionNum ber and SortQuestionAlp ha.

In the future, please state the entirety of your problem in your initial thread. This forum is manned by people who graciously volunteer their time. Asking how to sort, numerically, on a field containing digits only, when in fact the field holds alpha and nummeric characters, wastes posters' time, because different problems require different solutions.

Note that I've also changed the title to reflect the true subject of the thread.

Linq ;0)>

Moderator

[jennyp29]

Thank you very much, that works brilliantly. I am so sorry not to have put the whole question initially, just put it down to stupidity and old age, I am a silver surfer.