# Combining numeric fields with zeros on the left

This topic is 6561 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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.

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

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

Dan

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

This topic is 6561 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account