Problem with Sorting in a Text Field

[Redbeard]

I am looking for a way to sort a text field in my form in a specific order. The values I have in the field are either 1950.01.01 0r X1950.01.01 They now sort as:
1950.01.01
1951.01.01
1952.01.01
X1950.01.01
X1951.01.01
X1952.01.01

I want them to sort as:

1950.01.01
X1950.01.01
1951.01.01
X1951.01.01
1952.01.01
X1952.01.01

The form is controlled by a query that is pulling from several tables. Is it possible to sort this field in the query?

[Rabbit]

Do the sort on a calculated field where you strip off the x.

[gnawoncents]

I agree with Rabbit. There is probably a cleaner way to do this than my example, but to ensure you get the "X" records after the non-X, you could add the calculated field in your query like the one below. Then sort on the new column.

TextFieldSorted: IIf([Actual] Like "X*",Replace([Actual],"X","") & "X",[Actual])

[Redbeard]

Thanks for the response Rabbit and gnawoncents!
I have very limited knowledge of coding and this is a great help. In fact I didn’t even know that you could put this kind of code into a query, so thanks. This fixes the problem that I explained in my post, however I did not explain it well enough and so I am still not getting the order that I want (my fault). I will try and give a better example here. There are about 20,000 records in the database and this field has a unique number for all of them and it grows every day. The number that I have in this field is a three part number (1950.01.01) so within 1950 there can be 01 to unlimited and within 1950.01 there can also be 01 to unlimited. Here is a better example:
1950.01.01
1950.01.02
1950.01.03
1950.01.04
1950.02.01
1950.02.02
1950.03.01
1950.03.02
1951.03.03
1952.03.04
and so on…

What I need is for all the 1950’s to come before all the X1950 and then all the 1951’s and then all the X1951 and so on. If I did not have the X-files I could us a number field and that would solve the problem. So the code you provided is giving me this:

1950.01.01
X1950.01.01
1950.01.02
X1950.01.02
1950.01.03
X1950.01.03

And I need this:

1950.01.01
1950.01.02
1950.01.03
X1950.01.01
X1950.01.02
X1950.01.03

Is there anything I can do to fix this problem?

[Rabbit]

Add an additional sort after the one where you strip off the X where you sort by whether or not there's an X.

[Redbeard]

I am not sure how to do what you suggest Rabbit because my coding skill are somewhat basic, but I have been able to build on gnawoncents code and add a "Y" to the end of all the x-less numbers.

Expr1: IIf([Accession Number] Like "X*",Replace([Accession Number],"X","") & "X",[Accession Number] & "A")

However this also does not help me as it does the same thing as before.

1950.01.01A
1950.01.01X

What I need to do is to get the A or X after the first 4 numbers like so:

1950A.01.01
1950X.01.01

That way it will do all the 1950A's then all the 1950X's then all the 1951A's and so on. I think I need to use the mid function but can't make it work in the code. Can anyone help or show me an easier way of doing this... Driving me crazy!

[gnawoncents]

You can do this easily enough with two fields. Take the original field (Accession Number) and build two more columns in your query like so:

TextFieldSort1: IIf([Accession Number] Like "X*",Replace([Accession Number],"X",""),[Accession Number])

and

TextFieldSort2: IIf([Accession Number] Like "X*","X","")

sort by TextFieldSort2 then by TextFieldSort1. That way field 1 will be the primary and 2 the secondary sort. This should do it for you. Let me know if you have any other problems.

[gnawoncents]

Disregard my last post -- I need to look at it again. I'll get back to you.

[Rabbit]

ORDER BY Replace(FieldName, "X", ""), Left(FieldName, 1)

[gnawoncents]

Try this one. Add this field to your query and sort on it.

SortOrder: IIf([Accession Number] Like "X*",Replace(Replace([Accession Number],"X",""),".",9),Replace([Accession Number],".",8))

[Redbeard]

Thanks, gnawoncents your suggested code works great it has solved the problem! Replacing the “.” with “8 and 9” worked great! You have inspired me to work on my other problem with that field and were hoping you could help out. The problem is that I have this when my numbers reach 100 or 1000:
1950.01.10
1950.01.100
1950.01.11

And I would like this:

1950.01.10
1950.01.11
And so on till…
1950.01.100

So I created this code and put it into a new field in my query called SortOder2:

SortOrder2: IIf([SortOrder] Like "##########",Left([SortOrder],5) & "00" & Mid([SortOrder],7,1) & "00" & Mid([SortOrder],9)) & IIf([SortOrder] Like "########## *",Left([SortOrder],5) & "00" & Mid([SortOrder],7,1) & "00" & Mid([SortOrder],9)) & IIf([SortOrder] Like "###########",Left([SortOrder],5) & "00" & Mid([SortOrder],7,1) & "0" & Mid([SortOrder],9))

It runs off the code you gave me in “SortOrder” field and adds “0’s” into the right places so I get this:

1950.001.0010
1950.001.0011
And so on till…
1950.001.0100

The problem is when I switch the query to sort Ascending on SortOrder2 It asks me for a value for SortOrder. I am not sure why and it won’t run? So I tried to adding it to the end of the code in the SortOrder field but it just ignores the new code? Any suggestion?

[gnawoncents]

How are your numbers generated? We might be able to fix this earlier rather than later.

[Redbeard]

Do you mean how do I get the numbers in the "Accession" field? If so they are typed in by me, if not please expaine?

[gnawoncents]

Yes. That's really all I needed to know.

My recommendation would be to run an update query to add the zeroes directly into your Accession Number field so you have the same number of digits in all your records. Make sure to plan far enough ahead (are you ever going to have a .1000 or .10000?). This would keep your coding to a minimum and allow you to continue with just the one sort field.

Any other suggestions from the Experts out there?

[Redbeard]

I have considered doing that in the past gnawoncents (adding the extra zeros), however there are several problems with that. First, I have several other codes that run off that Accession Field which I may then have to reprogram for the new characters in the number. As I did not set up this database originally and I am not that great at coding, this could prove complicated and time consuming. Second, this number is also in other fields as part of other data. So, I would not only have to add the zeros to the Accession Field but also 5 other fields in the database. Third, each file gets printed out when complete as a hard copy with the number on each page. I would have 20,000+ files with the old numbers and all the new ones with the added zeros. This may look strange and confuse anyone looking at the hard copies as I will not be reprinting 20,000+ records. And finally, the extra zeros make the number harder to read in my opinion, and I have to use that field on a daily basis.
In a perfect world it would be nice if you could enter the number as 1950.1.1 and it display as 1950.01.01 and it was stored/sorted by 1950.0001.0001 This would be farther complicated by the “X” on the front of some of the numbers and the ”a-b” on the back of some. But I not sure that it is even possible to do that way and if it was I would probably need to build the database from the ground up. So, when I thought that I could just use a query to adjust the number and put it into another field that I could sort by, it seemed like the easier solution. If this is not possible or too completed I understand. If you have a better way of doing it let me know, I am all ears.
I have been trying this morning making a separate query to come up with the new number and then add the field with the new number from that query to my main query that runs my form and sort by that. Still not working but I keep trying.

By the way thanks for all the help!

[gnawoncents]

Thank you, I understand better now. What if you run the update query to a new field in the table ([Accession Number NEW] or something), then point the Sort query at that field? That way you maintain the original data, but also have easier data to work with on your sort. You could even set the update query to run automatically every time you open the database or some other form so your data will always be valid.

[Redbeard]

I took some time but it work! It have a sort query that updates to a field in my main table when I open the database. The field is then added to my main query that runs the main form and it sort it all in the right order.
Thanks gnawoncents for all your help! I could not have done this without it.

[gnawoncents]

Good work. I'm glad you were able to get it set up how you wanted. Let us know if we can help with anything else.