leading zeros, Int & Text ?

[shango]

I have a calculation that looks like this:

Right ("00" & Int ( shots::shot); 3)

This works fine for num fields.

However, I would like to use num and characters.

"001a" for example.

Can't seem to figure this one out.

[comment]

The question is not clear: is "001a" the given input, the required output (based on what), or what?

[shango]

sorry for being criptic.

I have a field where users will input values that look like this:

1a

1b

1c

2a

2b

2c

I need to add leading zeros automatically. so, I used a calulation that I found which inserts leading zeros just fine for a numbers only field.

Right ("00" & Int ( shots::shot); 3)

How can I add leading zeros in the case of a mixed

character field?

Here's what I need:

001a

001b

etc.

Thanks

Edited March 31, 2009 by Guest

[comment]

Just about the same way:

Right ( "00" & Yourfield ; 4 )

The result should be Text in BOTH cases.

[shango]

Thanks for the reply. That works nicely.

However, when I input a 3 digit numbers

the sort order gets screwy.

001a

0051

0101

051a

Any suggestions?

[comment]

That is the correct order, textwise. I am not sure what this is all about - it seems you have one field serving more than one purpose, and that is rarely a good thing. Perhaps you should have separate fields for the number and the suffix, and sort by those.

[comment]

Or perhaps you want the result to be:

001a

051

051a

101

[shango]

Yes, that is the order I'm after

These are shot numbers from a tv shooting schedule.

They look like this when sorted correctly.

008

008a

009

009a

009b

010

010a

010b

I'm starting to feel that my problem is not with the leading zeros calc, but rather with the sorting. Perhaps I need to make a seperate field for the letter increments. Is there a way around this?

Again, I appreciate your help.

Edited March 31, 2009 by Guest

[comment]

Well you could use:

SerialIncrement ( "000" ; Yourfield ) & Filter ( "abcdefghijklmnopqrstuvwxyz" ; Yourfield )

to get the above result, but I believe it's best to stick to the "one fact per field" rule.

[shango]

Thanks. I just went with an extra field for now.