Another Custom Sort Issue

Mrs.Meeker

Registered User.
Local time
Today, 12:34
Joined
Mar 28, 2003
Messages
172
I'm faced with another interesting sort issue. I have been studying about functions Left, Mid, and Right. I can't seem to get anything to work. I need to sort from beginning to end and not from left to right. I have numbers and letters both. I have read old posts and can't find any help. I had another sort issue that was turned around the other way and I can't seem to grasp the concept. I was also reading about the function Val () and can't find anything in help about it.

Anyway this is how the numbers are set up.

1.1a
1.1b
1.1c (etc)
1.2a
1.2b
1.2c (etc)
~~
10.1a
10.1b
10.1c

Having the same old trouble of sorting left to right with the ten before the two. Sort Ascending is not enough (of course!)

I've read I need to extract the numeric portion of the string. Format the numberic pieces with leading zero's "0000" and sort on the column of formatted numbers. I'm stuck!

Thanks for any help!
Rhonda

PS Can someone explain the function InStr to me?
 
Last edited:
Rhonda,

I built a query that has 3 fields the first field is your number i.e (1.1a, 1.1b). Lets call this FIELD1. This will be from the table that has the field.

I created a second field and looks like this:

Expr1: Val([FIELD1]) Sort this ascending.

I also created the third field and it look like this:

Expr2: Right([server],1) Sort this ascending.

This works. I tested it, but the query must have the fields in this order

FIELD1 (No sort)
Expr1 (Sort Ascending - No need to display)
Expr2 (Sort Ascending - No need to display)

Warning. Expr2 only works if there is one letter at the end of the field. If there is more than one letter, we need to do something else. Let me know if this works for you.
 
This is what I did:

Expr1: Val([tblQuestion]![Sheets])

Expr2: Right([server],1)

First of all I don't know what [server] means...?

Also when I tried to run the query I got an "enter parameter value tblQuestion Sheets message.

There could possibly be more than one letter at the end of the field in the future.

thanks!
 
Sorry, my fault, server1 should be the field name. So, using your data it will look like this:

Field 1: [Sheets]

Field 2: Expr1: Val([Sheets])

Field 3: Expr2: Right([Sheets],1)

In your query, if you are using just tableQuestion in design mode, you will not need to qualify the field with the table name. If you have to, the field name is separated by a ".", not a "!". Get this working and I have an idea about handling more than one letter at the end of the field.
 
Why not use:

Field1(not sorted): [yourtablefieldname]

Field2(sorted Ascending): Expr1: Format(Val([yourtablefieldname]),"0000.0") & Right([yourtablefieldname],Len([yourtablefieldname])-InStr(1,[yourtablefieldname],"."))

Here the InStr function returns a numeric value indicating the position a searched for character in string is found. In this case, InStr function is looking for the "." character in [yourtablefieldname].

Hope that helps...

Doug.
 
First, to theprez, This works and it's even easy enough for me to understand! :D Thanks, now what about extra letters?

And to Doug, This also works, but on display it repeats the first digit or group of digits, (not that is needs to be shown) but I don't know if it will cause issues in the future. I also don't understand how the code works. (I have alot to learn in the VBA arena!)~heck perhaps that's what is expected! See how much I don't know??!

Sheets Expr1
1.1a 0001.11a
1.1b 0001.11b
1.1c 0001.11c
~~~~~~~~~~~
17a 0017.017a
18a 0018.018a
18b 0018.018b
18c 0018.018c
18d 0018.018d

I'm not sure which of these choices I should use!
Rhonda
 
After more experimenting I think I should be using the code the "theprez" supplied. It's easier for me to understand and I'm still not sure about the output of Doug's. (sorry Doug)

Here's another example of that:

Sheets Expr1
1.2c 0001.22c
1.2d 0001.22d
1.2e 0001.22e
1.2f 0001.22f
1.2g 0001.22g
1.2h 0001.22h
1.2i 0001.22i
2.1a 0002.11a
2.1b 0002.11b

I really don't understand it at all.

Thanks to "theprez" for the help. If you have anything on the additional letters I'd be glad to try them. Thanks again!
Rhonda
 
Step By Step…

Hi, Rhonda.

It’s important to understand that when Access sorts strings, it sorts them alphabetically. When numbers are treated as strings - and any time numeric and alphabetic characters are joined together in a field, that field is implicitly a string - Access is going to perform an alphabetic sort. Therefore:

“1.1” comes before “11.1” comes before “2.1” the same way that
“b.b” comes before “bb.b” comes before “c.b”.

which is why we have to format the leading numeric part of your table field with “0” characters so that

“0001.1” comes before “0002.1” comes before “0010.1” the same way that
“aaab.b” comes before “aaac.b” comes before “aaba.b”

What all those function calls (Format, Val, Right, Len, and Instr) do in the code I suggested is this:

The first part of the code takes the string of characters in the table field and formats the leading numeric part of the table field into a new, separate string of uniform length (21.2 becomes 0021.2, 3.5 becomes 0003.5, etc.). That’s the ‘Format(Val([yourtablefieldname]),"0000.0")’ section.

The second part, the ‘& Right([yourtablefieldname],Len([yourtablefieldname])-InStr(1,[yourtablefieldname],"."))’ section, takes all the characters to the right of the “.” character in the table field and concatenates (joins) those characters to the string created in the first section.

So “21.2a” from the table becomes “0021.22a” in the calculated field of the query. Likewise:

“1.1a” ==> “0001.1” & “1a” = “0001.11a”
“1.1b” ==> “0001.1” & “1b” = “0001.11b”
“1.1c” ==> “0001.1” & “1c” = “0001.11c”

The Val function stops reading a string (in this case, the table field) at the first character it can't recognize as part of a number. The only reason for the second part of the code is to join to the “nnnn.n” string the characters that the Val function in the first section of code ignores.

How does this result in a valid (desired) sort? The same way as this alphabetical sort:

1.1aae
1.1abb
1.1ac
10.1a
2.1abe


results in a different sort order with the suggested code:

“1.1aae” ==> “0001.1” & “1aae” = “0001.11aae”
“1.1abb” ==> “0001.1” & “1abb” = “0001.11abb”
“1.1ac” ==> “0001.1” & “1ac” = “0001.11ac”
“2.1abe” ==> “0002.1” & “1abe” = “0002.11abe”
“10.1a” ==> “0010.1” & “1a” = “0010.11a”

Doug

PS: It’s very important that you understand the code you’re responsible for: you’re going to be the one that, well, has to fix it later (believe me, ther’ll be a later). Me & theprez are basically doing the same dang thing via different methods and styles. What I hope mor’n anything is you got some new insights in how to do thang’s your way. Go for it! And let us know...

;)
 
Last edited:
Hi Doug!

I am very interested in learning this. I am just on my way out of town (oh boy, SE Iowa!!) and will return in a few days. I will then have time to look at this more closely.

Just wanted to let you know, so you wouldn't think I didn't have any interest. I do want to learn to do these things the best possible way!!

I'll be in touch soon
Rhonda
 
Ditto. Besides your own skills, Doug and I are here. I will continue to monitor. And although my version is easier to understand, not sure if it holds when you start to have 1) multiple digits behind the period and 2) multible letters.
 
I've finally (nearly) cleaned off my desk after missing a week of work and now ready to dive back in.

I've been reading Doug's last post and have a question.

I understand the leading zero's.

I kind of understand those functions, 'still learning'.

I understand that 1.1a = 0001.1 & 1a but I don't understand 0001.11a

I understand that it putting them together but 0001.1a doesn't seem the same as 0001.11a and that's what is throwing me. I realize that 1.11a would equal 0001.111a and that it's not necessary to display, but could you explain why the extra digit is showing up and if it matters?

Thanks!
Rhonda
 
The reason the extra digit is showing up is because in the expression I suggested, the ‘& Right([yourtablefieldname],Len([yourtablefieldname])-InStr(1,[yourtablefieldname],"."))’ section finds the “.” character in [yourtablefieldname] and returns all the characters to the right of the “.” character.

If you need your table field ordered along those lines, a better expression to use in the query might be this:

Field1(not sorted): [yourtablefieldname]

Field2(sorted Ascending):Expr1: Format(Val([yourtablefieldname]),"0000.0") & [yourtablefieldname]

Is that any easier to understand?

Doug.

edited syntax...
 
Last edited:
I see that both expressions do technically the same thing - end result, correct sort. But this last one is cleaner and easier for me to understand.

Thank you very much!

Rhonda
 

Users who are viewing this thread

Back
Top Bottom