text field leading zeros

[Guest]

I need leading zeros inserted on a text field size of 11. I want to do this
in the table design view. Any help appreciated.

Thanks,
Annie

 

[Tom Ellison]

Dear Annie:

I assume you have a value that is at least 1 character long. Therefore, you
may need as many as 10 leading zeros.

So, first prepend the 10 zeros, then take the last 11 characters:

Right("0000000000" & YourValue, 11)

Does that do it?

Tom Ellison

 

[Guest]

I can get the leading characters to display as spaces, but not zeros. I have
entries into a field that can be numeric or text characters but the remaining
space has to be filled in with visible leading zeros. I am not a VB literate
person and need to use the format or input mask to fill the remaining left
spaces with zeros.

 

[DEBBRA ANDERSON]

I don't think what Tom is suggesting will work.
The only way I have ever been able to get this to work is to update the data
with a query. And that's after the fact, but that doesn't sound like what
you are looking for. You could also use an Event Procedure on a form field.
But anyway you look at it I don't think this can be done on the table.

 

[Guest]

I've come to the same conclusion.

I don't think what Tom is suggesting will work.
The only way I have ever been able to get this to work is to update the data
with a query. And that's after the fact, but that doesn't sound like what
you are looking for. You could also use an Event Procedure on a form field.
But anyway you look at it I don't think this can be done on the table.

 

[Tom Ellison]

Dear Annie:

If your column already has leading spaces, use LTrim() to remove them.

Did you try this in a query? Or is that where you want this to occur?
Perhaps you could explain the context of this problem.

Tom Ellison

 

[Guest]

The entire number (with leading zeros) has to be stored in a text field in a
table. The data will initially be imported intact - so that is not a problem.
I'd like to automatically have the zeros be inserted to keep the field a
consistent 11 characters long without having to rely upon the data entry
person to do it. I can live without it, but I would prefer to do it
automatically.

 

[Tom Ellison]

Dear Annie:

I'm not sure why you believe it MUST BE STORED with the leading zeros, but
that is possible. That would affect the sort order, and could be necessary
in relating rows in another table. Will users who alter this column or add
new rows be adding the zeros? Or would you be doing this when the control
with this value loses focus?

If you strip off any leading and trailing spaces and use what I suggested,
does that give the appearance you need? Is there some difficulty in
updating this?

Have you tried anything with respect to the solution I provided?

Tom Ellison

 

[Guest]

Dear Tom:

This is a primary key NDC number. It is always 11 characters long and can be
numbers or text. The leading zeros are part of the number, so they must be
stored.They indicate how the NDC field may contain UPC, PIN, etc. rather than
strictly NDC numbers and what they were converted from if they are not true
NDC's. For example, a certain number of zeros means it is actually a UPC code
and not a true NDC. The zeros are essential for idenfication purposes. My
biggest headache is manually entering new primary key fields as they become
available (the company does not want to wait a month for new feed and would
prefer to enter new products as they become available) so I need a way to
assure the leading zeros are entered correctly in an automated fashion. Does
this make it more clear? I really do have a sticky problem.

 

[John Vinson]

I need a way to
assure the leading zeros are entered correctly in an automated fashion.

Not to step on Tom's lines but... if you're entering the data using a
Form you can use a line of code in the textbox's AfterUpdate event.
Assuming the textbox is named txtNDC:

Private Sub txtNDC_AfterUpdate()
Me!txtNDC = Right("00000000000" & Me!txtNDC, 11)
End Sub


This will append enough zeros to whatever the user typed, and then
trim to 11 - so if the user enters "123" it will be stored as
"0000000123".

John W. Vinson[MVP]

 

[Guest]

This worked! Thank you so much. Sorry it took so long to reply.