alpha/number mix, not a good design but...

[MelB]

I am creating a new MS Access application to replace a very old DOS app (1984). It has a field called Form Number. I exported the existing data and imported it in Access. This field comes across as a text because in contains not only Forms, like 1,9,20,340, but also forms named 99R and CS2000.

Here is my problem... the client wants the new database to sort the records for a report 'numerically' on the Form field. The existing application sorts by what appears to be Numeric based on position. Here is an example...

1
3
7
16
20
101
304
A04
4000
6999
G321

This is the strangest thing I have ever seen... and I explained to the client that I've never seen an application sort this way. They really don't care and want it to sort this way anyway!!! Help!

What can I do... I thought about leaving it text and creating a second field in the report query with a function to pad leading zeros based on the length of the field... would this work? I'm not sure how to write this.

Can I somehow Right justify this field in the query and then sort based on that?

Any help would be greatly appreciated... Ane really, I do understand that it never should have been done this way but telling the client that is not an option...

 

[cpod]

You can create a field in your query that calculates the length of the field "Form Number" and sort on that as the first key and the "Form Number" field as the second key.

 

[The_Doc_Man]

The only way to do this that comes to mind involves parsing the form-number field. But in order to do that you need to know some facts about the thing you are sorting.

Is there a limit to the number of characters involved? (I.e. must there be at least 1 digit?)

Is there a limit to the number of digits involved?

Supposing there exists a form A304 and G304 (just as an example...) Will it always occur that you sort by the numeric part first and the text part second?

At least one of your examples has two letters and some numbers. Will it ever occur that you have numbers followed by letters (as well as letters followed by numbers?) Can you ever have letters, numbers, then more letters? What would be the sort order in these cases?

(Actually, I don't want to know - but if YOU don't know the rule, you'll NEVER be able to program it no matter WHAT you do.)

We had a case that was terribly ugly with regard to sort. I think your customers and mine must have been related. Anyway, this bizarre sort depended on rules we could never figure out. They told us the order they wanted so we just said, OK, you got it.

As a method of last resort, we created a field called LineNumber (or something close to that, I forget at the moment.) We entered numbers to the field in the correct order for the sort. When we had to present the items in the specific order, we used a query that sorted on (but did not display) the LineNumber field as the first sorted field.

A thought that might help you if you don't mind getting a little bit of VBA dirt on your elbows...

Write a public VBA string function that reformats this bizarre key into something else of fixed length, with e.g. 5 digits followed by 2 or 3 letters (or however many of each you need) with leading zeros for the digits and trailing blanks in the letters portion. In other words, pad to the left with 0, to the right with blank, then extract the middle portion that contains your number/letter combo. Then write a query that uses that function as the basis for its sort order, whether or not you actually ever display the result of the function.

Basically, you would have to examine each character of the key in order, decide whether it was a digit or a letter, and append it to one of two strings. Then compute the value of the digit string with a CLng function, generate a leading-zeros string with FORMAT function. For the letters field, add some silly number of spaces, then take the LEFT$(string, 3) (or 4 or whatever the correct length turns out to be...).

Now concatenate the digit sequence with leading zeros and the text sequence with trailing blanks. Return the resultant string as the value of that function. And if it is a public function you can use it in queries, forms, and reports!

 

[RV]

The_Doc_Man has made some good points (I'd be disappointed if he hadn't..)

Nowever, I guess the reply from cpod will do the trick just fine (considering it's an old DOS applic which is used as a basic start point...)

Your statement would be something like this:

SELECT FormNumber
FROM YourTable
ORDER Len(FormNumber), FormNumber
;

RV

 

[MelB]

Excellent suggestion cpod!!! It worked like a charm... thanks!!!

You can create a field in your query that calculates the length of the field "Form Number" and sort on that as the first key and the "Form Number" field as the second key.