Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Formating text in access

Jul 12, 2005 2:12AM PDT

This may be a simple matter but for some reason I just cant figure it out. I have a field in access that I want to keep as text, but the contents of the field is numbers. I want every entry to be 10 digits. If the contents are less than 10 digits I want to have zeros padding the front. Is there an easy way to do this?

Discussion is locked

- Collapse -
Formatting numbers in Access
Jul 12, 2005 3:17AM PDT

Hi there,

Am I right in presuming that the zeroes on the front are purely for display purposes, so that when you view or print your data, the number is always displayed with zeroes on the front for numbers less than 10 digits long?

Can I also take it that your field is currently of data type "Text" and not "Number"?

If yes to both, then I strongly suggest converting your field to "Number". Then in the field property "Format", type in ten zeroes. If you have any forms or reports which already use this field, also change the "Format" property to be ten zeroes.

Good luck... Richard

- Collapse -
Thanks, but It still doesnt work
Jul 12, 2005 4:17AM PDT

Thanks for the reply. The reason I need the 10 digits is because I need to compare the list I am trying to convert to another database that has the values listed in the 10 digit format with the zeros leading. When I compare the 2 databases to find the non matching entries, the entries missing the leading zeros also come up. The field is currently text, but when I convert the field to number, I lose about half my entries. For one reason or another access delets them. Any other suggestions? Thanks again.

Peter

- Collapse -
In that case...
Jul 12, 2005 7:26PM PDT

From your answer I take it that the answer to my first question is 'No' - the zeroes on the front are not just for display purposes - so my first suggestion doesn't necessarily apply.

As I understand it, you are making comparisons against a field in a different database/table whose datatype is also Text and has leading zeroes before the significant number to comprise the ten-digit length value. You need to ensure that the values are like-for-like in order to make proper comparison.

If so, then I suggest you use an Update query to insert the extra zeroes on the front of each number. You might want a backup of your table before you proceed.

Create an Update Query against your table and drag down the field in question (the one that needs the zero digit padding - I'll refer to it as 'TextNum' for this exercise) onto the grid. In the "Update To:" box, enter the following expression:

Right$("0000000000" & [TextNum],10)

(Instead of typing 'TextNum', type the actual name of your field.)

Then execute the query (Query -> Run) and see how you go.

Good luck - Richard

- Collapse -
THANKS!!!
Jul 15, 2005 2:03AM PDT

Thanks! that worked perfectly