Combining numeric fields with zeros on the left

[alopas]

Hi,

I have two fields that I am triying to put together:

"Prefix": 3 letters text

"Primary code": auto-enter serial number

I have defined "Secondary code" as a calculation field as follows:"=Prefix & Primary code"

The problem is that "Secondary code" doesn't show the zeros on the left of "Prefix". E.g.

Prefix=ALP

Primary code=00100

Secondary code=ALP100 (and I want it to be "ALP00100")

Any ideas on how can I solve this problem? Thanks a lot in advance.

[Girwin]

One way would be to change the field type of Primary Code to text. It can still serialize as before, but it will show all of the leading zeros.

[spb]

Instead of changing the field type of primary code (it's already an auto-enter), make it part of the calc in secondary:

Prefix & NumToText(Primary code)

However, that probably won't work if the primary code field is defined as a number in the first place. If you get the leading zeros to display, they still won't be part of the true numerical data in the field, so NumToText can't work on it. What you need is a way to force (within the secondary calculation) the information concatenated to Prefix to always be five characters, with zeros added to pad to the left. Here's one way:

Prefix & NumToText(Case(

Length(PrimaryCode)=1, "0000" & PrimaryCode,

Length(PrimaryCode)=2, "000" & PrimaryCode,

Length(PrimaryCode)=3, "00" & PrimaryCode,

Length(PrimaryCode)=4, "0" & PrimaryCode,

PrimaryCode))

Steve Brown

[DanBrill]

Steve is on target, but there's an easier way. Calc with result as text:

Prefix & (right ("00000" & PrimaryCode, 5))

Dan

[spb]

Every time I think of a way to get something done with six lines of script or calculation, some wise guy comes along and shows how to do the same thing in one line.

Nice tip, Dan.

Steve Brown

[DanBrill]

Glad to oblige. I only picked up this way a couple of months ago, From John C I think, who himself had only just picked it up from Queue, I think. I'm sure if I went back through all of my scripts I'd find many times I'd done it the way you'd suggested. You see it and think "now why didn't I think of that?"

Dan