Custom Order

[Mrs.Meeker]

My data needs to be sorted a little differently. First all topics beginning General 1, General 2, General 3, etc., then A1, A2, A3, B1, B2, B3, C1, C2, C3....

Is there an easy way I can accomplish this?

Thanks, Rhonda

 

[dcx693]

Use a query to feed the data into your form and use a calculated column in that query that slightly changes the topic field so that they will sort in the right order. Perhaps use a formula that subsitutes a value of "_ 1" for General 1, etc... because "_ 1" will sort before "A1". The only complication might be: how many General categories could you have?

Here's a formula you could try if you had 9 or less General categories:
IIf(Left("[topic]",7)="General","_" & Right("[topic]",1),"[topic]")

 

[Mrs.Meeker]

There are 185 lines of information on this form. General 1 through 10 and who knows how many A1-? B1-? C1-?...through the whole alphabet, with the possibility of it continuing. AA1-?BA1-? etc. I want them to show up on the form in this order. Over 10 really throws a wrench in. It comes out 1, 10, 2, 3, 4...
That left to right sort issue. The General catagories could continue to increase. Currently there are 10.

 

[dcx693]

Having > 10 categories is no big deal, just add leading zeroes to the numers in the "dummy" sort string. Will there always be a space between the word "General" and the number of the category?

Do you understand the solution I proposed, at least in concept?

 

[Mrs.Meeker]

Not really, I'm still rather new to this and trying to learn. Would I put this formula in the criteria in the query? I'm not sure I know what a calculated column is.

 

[dcx693]

To create a calculated column, you'd place that formula into the "Field:" line of a blank column. Access will give the column the default name "Expr1", but you can change that name by changing the part "Expr1" part.

 

[Mrs.Meeker]

I'm sorry to be so dense...I placed the formula in an empty field in the qryQuestionsSubform query. I changed "topic" to PageCode and the column contains just that. [PageCode]

What did I do wrong?

 

[dcx693]

Rhonda, there's a good section in the Access help called "Create a field that performs custom calculations or manipulates field values in a query". Check it out.

 

[Mrs.Meeker]

thank you.

 

[dcx693]

It should give you a good intro into how to calculate a value. You can then use that value to sort on, even if it's never displayed in the form that uses the query. Write back if you have questions.

 

[Mrs.Meeker]

I'm so lost that I'm embarressed to ask for any more help.
I don't know why I don't get it!

 

[dcx693]

Let's take this one step at a time.

Access does not support custom sort orders. I am proposing a workaround that sorts on a dummy calculated column.

Now as for the mechanics of this: You have a form. What is the recordsource for the form? It must either be your table or query that is feeding records into that form.

 

[Mrs.Meeker]

It is a query. qrySurveyQCSubform

Thanks for being so patient.

 

[dcx693]

No problem....

OK, so did you create the query yourself? If not, do you know how to create a query? If so, do you understand the concept behind creating a calculated field in a query. There are many reasons to do this. Let's say you have unit cost and sales quantity and you want to find total sales. You'd have to do some math in there. A calculated column in a query is what you want to use.

 

[Mrs.Meeker]

No, I did not create this query. I've been helped along with this from a coworker who knows a great deal more than I do. I know how to make simple queries but I don't completely understand the concept behind creating a calculated field in a query. I've been reading in the Access Bible, but i'm having trouble relating it to a custom sort.

Some of my queries are kind of hit and miss. If it doesn't work I try again

 

[dcx693]

If it doesn't work I try again

Well, that is a good way to learn.

Don't try to relate the custom sort to the calculated control yet. First understand what a calculated field is and why it's useful and necessary to certain databases. When you're performing math or otherwise manipulating data, a calculated query is often where it's done.

I think you understand that your original topics list would not sort the way you wanted it to. Access sorts items alphabetically or numerically. But "General 1" does not come before "A1". You need it to. The workaround: create something else from your data that you can sort on.

Let's say you've got these as your topics:
General 1
A1
A2
General 2

I'm saying to create a calculation that produces a new field:

topic       new field
General 1   _1
A1          A1
A2          A2
General 2   _2

You can now sort on the 2nd column. The original data is still there and unaltered. Why did I choose something nonsensical like "_1"? Only because the special characters sort before alphanumeric characters. You could transform it into ">1", or "!1", or "^1" if you wanted.

 

[Mrs.Meeker]

Okay, that makes sense, but how to do it does not. Your formula:

IIf(Left("[PageCode]",7)="General","_" & Right("[PageCode",1),"[PageCode]")

I don't understand the numbers (7 & 1).
I assumed that "topic" would be the field name that I want to sort. So I changed it to PageCode

What I think I'm reading is that if the left "half" of that field is General then change it to _ but then I don't understand the rest of it.

I don't want to jump ahead here, but where does this go? (in a new query, as an addition in an old query?) (If I got it right)

 

[Mrs.Meeker]

I know what the numbers mean...it's the number of digits left, or right! Duh!

 

[dcx693]

Just so we get the field names right, what is the name of the field that contains the data "General 1", "A1", etc...?

OK, so the task is to change instances of "General 1", "General 2, "A1", A2", etc... into "_1", "_2", "A1", A2", etc...

You need to find out which of those begins with "General". For that, you need the Left() string function. This fornula: Left("General 1",7) results in "General". This formula:Left("A1",7) results in "A1". That's what the number 7 is used for.

Now that I've successfully found which of the data begins "General". But there are more than one type of entry that begins with "General". There's "General 1", "General 2", etc... We still want them to sort correctly. We need the number. That's where the Right() function comes in. This formula: Right("General 1",1) results in "1". That's where that number 1 comes in.

If you have "_" and you have "1" and you want to concatenate them to form "_1", then you use the ampersand operator like this: "_" & "1". That produces "_1".

We still need to handle the situation where you could have "General 10", General 11", because then we can no longer use Right([yourstring],1).

 

[Mrs.Meeker]

The name of the field that holds General1, A1, etc is called PageCode.

And I apologize, there is no space between General and 1 if that makes a difference.

As you can see from my last post, it takes awhile for things to sink in. I know that from Excel! I guess I'm reaching the age where my little brain is beginning to shrink!

Okay! So I understand what you are saying (though I'm not sure I'm ready to write my own formula yet).

You are a great teacher!