Custom Order

[dcx693]

Thanks. I try!

To handle the "general" case where the number after "General" might be larger then 1 digit, we just need to use a formula like this: Mid([PageCode],8). Can you figure out why? And then assemble the complete formula? See what you get from Mid("General1",8) and Mid("General12",8).

As I noted before, the formula goes into a blank column in your existing "qrySurveyQCSubform" query on the Field: line.

 

[Mrs.Meeker]

Mid([PageCode],8). Can you figure out why? And then assemble the complete formula? See what you get from Mid("General1",8) and Mid("General12",8).

Oh boy, now you got me lost. Mid, is that the middle of the formula? The 8 has me lost. I haven't gotten anything to work yet with the formula, and I just made it worse.

 

[Mrs.Meeker]

It's time for me to go, I have a 45 minute commute and a big dog waiting to go outside. Can we pick this back up tomorrow. If I have time I may be able to work on this tonight. not sure.

 

[dcx693]

I already went home and went for a 45-minute run.

Now I'm back. Try this formula out:
IIf(Left([PageCode],7)="General","_" & Mid([PageCode],8),[PageCode])

Look up the IIf function in the Access help. It's very useful.

Attached is a picture of how the calculated field should look when it's entered into your query.

 

[Mrs.Meeker]

If I ran 45 minutes I'd drop dead, sure glad I have a lazy dog!

I tried your formula and got the following message: The specified field '[PageCode]' could refer to more than one table in the FROM clause of our SQL statement. That qry is build with tblQuestions and tblQuestionsResults. So I changed the formula to:

Expr1: IIf(Left([tblQuestions.PageCode],7)="General","_" & Mid([tblQuestions.PageCode],8),[tblQuestions.PageCode])

And guess what...IT WORKED!!! I'm so proud! Morning is my best time of day, by 4pm I'm feeling rather googly eyed!

Then I changed the sort to ascending...am I jumping the gun here?

But I'm still having trouble with the General 1, General 10, General 2, General 3, etc.

 

[dcx693]

Cool. But I thought you wrote there was no space in between "General" and "1". Are the topic names like "General 1", "General 2", or are they like "General1", "General2" etc....?

 

[Mrs.Meeker]

Gawd I really was googly eyed last night!!! It looked like it then, but I was wrong. I'm sorry!

It's General 1, General 2, etc...

 

[dcx693]

OK, no big deal. You can actually leave the formula the way it is. When applied to "General 1" and "General 2", the formula:
IIf(Left([PageCode],7)="General","_" & Mid([PageCode],8),[PageCode])
will produce "_ 1" and "_ 2". There are spaces between the underscore characters and the numbers. That's not a problem since they'll still sort before the entries like "A1" and "B2".

If you're anal, and I am, you can change the formula to:
IIf(Left([PageCode],7)="General","_" & Mid([PageCode],9),[PageCode])
just to get rid of that space.

Or you can even change it to:
IIf(Left([PageCode],7)="General",Mid([PageCode],8),[PageCode])
taking out the "_" character entirely because " 1" and " 2" (the numbers with spaces in front of them) will still sort before "A1" and "B2".

 

[Mrs.Meeker]

If you're anal, and I am, you can change the formula to:
IIf(Left([PageCode],7)="General","_" & Mid([PageCode],9),[PageCode]) just to get rid of that space.

I guess I'm a little anal too...

I've been reading about the "mid" part of the formula. Since my 10's are still before my 2's.

MID
Returns a specific number of characters from a text string starting at the position you specify.

Syntax

MID(text,start_num,num_chars)

Text is the text string from which you want to extract the characters.

Start_num is the position of the first character you want to extract. The first character in Text is 1.

Num_chars specifies how many characters to return from Text.

It kind of loses me there toward the end...? Would it be something like this?

Mid([PageCode],9,2)

 

[dcx693]

Aha, so are you getting "General10" sorting before "General2" because "_10" sorts before "_2"?

I forgot about that. No biggie, but how many "General" topics could there be? You said there could be more than 10, but could there be more than 99? More than 999? More than 9999?

The easiest way around this problem is to add some leading zeroes to the digit part of the calculated field so that you get "_10" and "_02". Those will sort correctly.

By the way, if you omit the last parameter from the Mid function, as I did, it causes the Mid function to take all characters from the string from the starting position you specify until the end of the string.

 

[Mrs.Meeker]

I doubt there would ever be more than 99. But I don't follow you. Were do I put the leading zeros?

add some leading zeroes to the digit part of the calculated field. - I've tried a couple things that didn't work...so I'm wondering where...

IIf(Left([PageCode],7)="General","_" & Mid([PageCode],09),[PageCode])
I put the zero before the 9 and it didn't do anything and when I went back the zero was gone.

By the way, if you omit the last parameter from the Mid function, as I did, it causes the Mid function to take all characters from the string from the starting position you specify until the end of the string.

Believe it or not, i actually understand this part!!

 

[dcx693]

OK, try version 300 of the formula :
IIf(Left([PageCode],7)="General","_" & Format(Mid([PageCode],9),"0000"),[PageCode])

See if you can figure out what changed and how it works. It puts at most 3 leading zeroes in front of numbers and will work until your numbers surpass "9999".

 

[Mrs.Meeker]

Hey that's cool!

The "0000" is the number of characters in the mid statement to return....?

But what is Format? The format of the string? 0001?

This only worked on the General * questions.

Now I have to deal with my A 1, A 10, A 2, B 1, B 10, B 2, etc.

So do I somehow nest Left("A1",7) within the formula. Well, wait a minute that would only cover those that start with A, but I have lots of them. And at the end it's W-Z ##.

Is there a wildcard I can use instead of a letter? Like *

Would I need to add 2 different ones, because they are all single letters until I reach the bottom and then it's one W-Z 1, W-Z 10, etc.
???

 

[dcx693]

The formula should work for items like "A1", "A2", "B1", etc... because it checks to see if they begin with "General...".

 

[Mrs.Meeker]

It does except the 10's come before the 2's

Did I do something wrong?

IIf(Left([tblQuestions.PageCode],7)="General","_" & Format(Mid([tblQuestions.PageCode],9),"0000"),[tblQuestions.PageCode])

 

[Mrs.Meeker]

It's got something to do with that 9??

 

[dcx693]

That's weird. In my test database, the input data is:
PageCode
General 1
General 2
General 10
A1
B2
C1

The sorted calculated data is:
Expr1
_0001
_0002
_0010
A1
B2
C1

Are you results different?

 

[Mrs.Meeker]

Yes, try adding
A 1
A 2
A 3
A 10
A 11
B 1
B 2
B 10
C 1
C 2
C 10

Or something like that

I'm getting

A 1
A 10
A 11
A 2
A 3
B 1
B 10
B 2
C 1
C 10
C 2

I guess I didn't have to get so carried away!

 

[dcx693]

Crap. You're right, I forgot about the multidigit "A10" and "A2". Would you ever have multileter topics? Like AA1, BE12, etc..??? Or only single letters with numbers?

 

[Mrs.Meeker]

I hope not, but in the future I guess it might be possible. I would think if it happened it would be AA 1 AA 2 AA 3, I would think that it never went over 2 letters.