By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 18
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?
Oct 28 '08 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
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:

SortQuestionNumber: CInt([QuestionNumber])

replacing

QuestionNumber

with the actual name of your field. Then sort by

SortQuestionNumber

Ditto in Report Design, sort on the calculated field.

Welcome to Bytes!

Linq ;0)>
Oct 29 '08 #2

P: 18
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.
Oct 29 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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:

SortQuestionNumber: Val([QuestionNumber])

Then Create this field:

SortQuestionAlpha: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

SortQuestionNumber: Val([QuestionNumber])

to Ascending and then set sort order of

SortQuestionAlpha: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, SortQuestionNumber and SortQuestionAlpha.

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
Oct 29 '08 #4

P: 18
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.
Oct 30 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.