Sorting Numbers

[jereece]

I have a report that shows data from an SQL Database that I only have read access to. The report has Event Codes comprised of letters and numbers. When I run the query that my report is based on, they don't sort properly because there is insuffient leading 0s. Below is an example of how the report will sort the event codes.

F1
F10
F11
F12
F2
F22
F3

How can I get these to sort properly in my report??

As always, I appreciate the support.

Jim

 

[RuralGuy]

And another approach thanks to Uncle Gizmo's example.

 

[jereece]

I am getting error messages when I tried either one. It may be my fault. Let me give you additional examples of the codes. I think the most would be one letter, a 2 digit number, then a 2 digit letter.

F1a
F10cc
F11s
F12t
F2b
F22nn
F3c

 

[jereece]

Well thats what happens when you only supply 1/2 of the data...

I admit it. I screwed up. My bad. But can you please help?

Thanks,
Jim

 

[jereece]

The first character is always a letter, the next character will always be a number. The third character may be a number or a character. The fourth and fifth will always be a letter. Below are possible examples

F
F1
F1a
F1ab
F11
F11a
F11ab

Again, I appreciate the help.

Jim

 

[jereece]

Yes the third character is the stumbling block.

Lets take a little different approach. What if I used an append query to copy the data down locally to a table. Is there a way I could apply formatting to the code table so that it would know that the first character is a letter, then if the next two characters is not a number, then apply a leading "0" first?

I appreciate any thoughts on how to do this.

Jim

 

[RuralGuy]

How about this example?

 

[jereece]

That does not work because F11aa sorts before F11a which sorts before F11. Also adding additional numbers results in G1 sorting before F1, G11 before F11, etc. See attached.

 

[RuralGuy]

How about this sample?

 

[jereece]

That appears to work!! I can not believe that it's that simple. That's just unbelievable. I have tried and tried to figure a way to do this. I'm just amazed.

I really appreciate the help.

Jim

 

[RuralGuy]

Glad I could help.