On Exit event procedure string to add 3 leading zeros to a # ?

[Guest]

I need a string for an On Exit visual basic code to add 3 leading zeros to
any numbers entered in that field.

For example, if I enter 1234567, the on exit event will change it to
0001234567.

Thanks

 

[Douglas J. Steele]

If they're actually numbers, you can't: numbers aren't stored with leading
zeroes.

If they're text, you can use Me.MyTextbox = "000" & Me.MyTextbox

 

[Guest]

When i enter this code it does place three zeros in the field, but it writes
over the existing number. How do I get the zeros to tack on to the front of
the number sequence instead of replacing the number sequence?

 

[Guest]

As Douglas stated, numbers will not retain leading zeros, only text. If you
have a number and want to put the leading zeros on it, you will have to
convert the number to text. Even if you do that and try to store it in a
numeric field, it will loose the leading zeros.

 

[Vincent Johns]

When i enter this code it does place three zeros in the field, but it writes
over the existing number. How do I get the zeros to tack on to the front of
the number sequence instead of replacing the number sequence?

By deciding where you want to put the result, and then putting it there.
You're going to have to put it somewhere in your system that has a
name, otherwise you'll never see it again. I suppose the "bit bucket"
has a name, but what you need instead is a field in a record in some
Table, or a control on some Form, where you can store the result of a
calculation.

Incidentally, by "number sequence", are you referring to the sequence of
digits that forms the number displayed in the control, instead of a
collection of values in a field to which you want to add more values
(the new ones containing the "000" prefix)? It's not clear.

 

[Guest]

It is currently a text field. I tried Douglas' string but it replaced my
existing number sequence with "000" instead of adding the leading zeros to
the existing number sequence. How do I add instead of replace?

 

[Guest]

Me.MyTextField = "000" & Me.MyTextField

It is currently a text field. I tried Douglas' string but it replaced my
existing number sequence with "000" instead of adding the leading zeros to
the existing number sequence. How do I add instead of replace?

 

[Guest]

Sorry, by number sequence I mean this:

I put in the following pledge number in my text box: 123456
I want the pledge number to read like this on an On Exit command: 000123456

What do I need to vary from Douglas' string?

 

[Douglas J. Steele]

What does your code look like? What I gave you will definitely do what
you're trying to do.

 

[John Spencer]

UHH, Guys and Galls!!!

But it will add three leading zeroes EVERY TIME you Exit the control unless
you add some test in the code to prevent that.

 

[John Vinson]

I need a string for an On Exit visual basic code to add 3 leading zeros to
any numbers entered in that field.

For example, if I enter 1234567, the on exit event will change it to
0001234567.

Thanks

Just another suggestion: if you want the field to always contain ten
bytes, zero filled (so that entering 31 actually stores 0000000031),
use the textbox's AfterUpdate event:

Private Sub txtMyTextbox_AfterUpdate()
Me!txtMyTextbox = Right("0000000000" & Me!txtMyTextbox, 10)
End Sub

Note that this will truncate the entry if the user types in something
longer than ten bytes.

John W. Vinson[MVP]

 

[Douglas J Steele]

Agreed, John, but without further details (like "I always want it to be a 9
character string", or "I'll never type a 0 as the first character myself"),
there's not much that can be done about that.