If Field = "5", Need Field = "00005"

[tbfilemaker]

How can I create field that can hold up to five digits that automatically puts zeros in front of the number. Example, 5 = 00005 and 14 = 00014 and 200 = 00200. Thanks in advance!

[Fitch]

Is this an automatic serial number? Just set the initial serial number to 00001 and away you go. Number fields will ignore the leading zero when searching and sorting, but text fields won't, so I'm guessing you'll want to use a text field.

If you have an existing field you want to add zeros to, make a calculated text field like this:

Right("00000" & YourNumber, 5)

[Peter Fenner]

I would solve this the following way

My Number Entry = a number field

My Calculated Number = a calculation field

The calculation field would be described as follows:

code:

---

If (My Number Entry <= 9, "0000",

If (My Number Entry <= 99, "000",

If (My Number Entry <= 999, "00",

If (My Number Entry <= 9999, "0",

"")))) & My Number Entry

On my layout I would place the calculation field (My Calculated Number) directly on top of the number field (My Number Entry). I would set the calculation field to NOT allow entry into field (set this in field format). The calculation field must also be set with a fill color (not transparent). The number field must be set to ALLOW entry into field.

This will have the effect you desire. Ofcourse, when you click in the field the number will show without the added 0's.

Also if you have to perform finds on the field with the added 0's you will need to use the Go to Field (My Calculated Number) to gain access to this.

Cheers

[Peter Fenner]

Fitch's last line on his reply is most apt - my calculation was not the path of least resistance.

Aside from this - what is the newsgroup protocol on giving one's suggestions - even when their are more experienced forum members who may have better solutions?

[Fitch]

Protocol... we don't need no steenkin' PROTOCOL!

Peter, if everyone was worried about giving the perfect answer to every question, nobody would be answering questions! Except maybe Vaughan...

Anyway, I think your solution in this case doesn't break any known laws of physics... although my own preference would have been to use the Case() function...

...but in the future I must insist that you adhere more closely to the POLR Protocol.

[Kurt Knippel]

quote:

---

Originally posted by Peter Fenner:

code:

---

If (My Number Entry <= 9, "0000",

If (My Number Entry <= 99, "000",

If (My Number Entry <= 999, "00",

If (My Number Entry <= 9999, "0",

"")))) & My Number Entry

---

Oh my dear GAWD! that is horrible. Imagine if you need to add or remove a zero or two.

Do the following:

Right ( "000000" & My Number Entry, 5 )

this will take whatever your number is and pad the calculation out to 5 or whatever you need it too be.

[Vaughan]

<hears his name being spoken> What? Me giving perfect answers? Ha!

Seriously, I do have an aesthetic for elegance and simplicity, for portability and "fit" -- though I cannot explain what I mean by fit except that things work well when they "fit" into a solution's wider context.

Apart fom that, most of my posts are speculation. I often make a little test database to try some idea or process, but mostly the answers are suggestions based on experience -- my experience and the experience of list contributors that have been generous enough to share through the Forum.

I learn more from this Forum that I contribute, and I contribute a lot.

[tbfilemaker]

Thank you kindly for the solutions provided, it works like a charm! I love your guys' humor, and appreciate the input! Happy FM8KG

[Peter Fenner]

CaptKurt,

Yeah I know its pretty awful! - but help me out - What do you mean by adding a zero or two? I'm really curious and eager to learn more from you seasoned guys.

Thanks!

[Kurt Knippel]

In your way adding 2 places results in:

code:

---

If (My Number Entry <= 9, "000000",

If (My Number Entry <= 9, "00000",

If (My Number Entry <= 9, "0000",

If (My Number Entry <= 99, "000",

If (My Number Entry <= 999, "00",

If (My Number Entry <= 9999, "0",

"")))) & My Number Entry

See how this can be come very big and unnecessarily complex? And this is a VERY SIMPLE example of the kind of evaluation that may be needed.

Contrast that with adding 2 places to my calc:

code:

---

Right ( "00000000" & My Number Entry, 7 )

See the difference?