Jump to content

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.

Link to comment
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

Link to comment
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?" smile.gif

Dan

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.