Adding Leading Zeros

[jo15765]

I have a field that I need to be 5 characters. Access dropped the leading zero's when I imported the spreadsheet, and at the time I didn't think it was an issue, but now I realize it is. Is there a way that I can add the leading zero's into the field in my database?

[pbaldy]

A numeric data type can not hold the leading zeros, only a text data type can. You can get them with:

Format(FieldName, "00000")

either for display or as part of a process to update the field.

[jo15765]

I am an access beginner, where would I put that code in Access 2000 to make it display the leading "0"

[RuralGuy]

It looks like Paul might be getting some zzzzz's. Where do you want to display this numeric value with leading zeros?

[jo15765]

i am wanting the leading 0's to be displayed on my form. my table does not show them either, so it makes sense for me to have to add them to the table first, then redesign the form. but how?

[RuralGuy]

As long as you just want to display the value and not alter it then Paul's suggestion in post #2 as the ControlSource of your control will work just fine. =Format([FieldName], "00000")
This just alters how the field displays on this form and does not alter the field in the table.

[jo15765]

Where do I go to add the Control Source? That is what I am not understanding, I do not know where to input this code in order to make it display the leading zero's.

[pbaldy]

You can enter that directly in the textbox in design view, or go into the textbox properties (double click on it) and enter it in the Control Source property on the Data tab.

[jo15765]

When I enter =Format([SKP B], "00000") in the Control Source, it displays as #Error

[pbaldy]

Make sure the textbox doesn't have the same name as the field. That can confuse Access.

[jo15765]

I have named the field SKP 2 and the Textbox remained named as SKP B, but it is still showing me #Error when I input =Format([SKP B], "00000") into the Control Source box.

[pbaldy]

You want the name of the field, not the textbox. Try

=Format([SKP 2], "00000")

The spaces in your names are not worth the bother in the long run.

[jo15765]

This may not matter, but do I need the text format set as plain text or rich text? It is set as plain text, but it isn't working set either way. Also, I have the field set as text.

Now I receive the following error:

#Name?

I'm curious, why do you say the spaces in names are not worth it?

[jo15765]

I couldn't get this to work, by adding the code to the ControlSource, so I built a query and added this line into the SQL:

=Format([SKP B], "00000")

and it added my leading 0's

All thanks for the help!