Calculation Question

[Database Designs]

I have a file with values such as A1,A2,A3,A4...A200. I want to create a "Sort by" field that will contain the corresponding number of " " spaces based on the number in the value.

Ex. For A6 I want 6 spaces in my "Sort by" field.

What calculation do I need to do this.

[TheTominator]

If your field with values "A1", "A2", ... "A200" is named MyField, then you can try...

Right(" (200 or more spaces between the quotes) ";

GetAsNumber(Substitute(MyField; "A"; "")) )

Requiring a lot of spaces is a little odd. Are you sure there's not an easier way to accomplish whatever you are going for?

For example, to generate a numeric sort field you could create a calculation that returns a number like this.

GetAsNumber(Substitute(MyField; "A"; ""))

[Database Designs]

What I'm trying to do is create a value list that will display A1,A2,A3...A200 but sort it numerically. Any suggestions?

[TheTominator]

How about add leading zeros to the numeric part?

A1 --> A0001

A2 --> B0002

...

A200 --> A0200

If the code always starts with "A" you could do

SerialIncrement("A0000"; GetAsNumber(Substitute(MyField;"A";""))))

or use the old technique of prefixing zeros manually

"A" & Right("0000" & GetAsNumber(Substitute(MyField;"A";"")); 4)

[comment]

I have a file with values such as A1,A2,A3,A4...A200. I want to create a "Sort by" field that will contain the corresponding number of " " spaces based on the number in the value.

Cannot be done: the "spaces method" is limited to around 100 spaces. Values above that will get identical entries in the index.