What is the appropriate data type to use for storing numbers with leading zeroes?

Question

In Access 2003 I need to display numbers like this while keeping the leading zeroes:

• 080000
• 090000
• 070000

What data type should I use for this?

Answer 1

Use a string (or text, or varchar, or whatever string variant your particular RDBMS uses) and pad it with whatever character you want ("0") that you need.

Answer 2

Key question:

Are the leading zeros meaningful data, or just formatting?

For instance, 07086 is my zip code, and the leading zero is meaningful, so US zip codes have to be stored as text.

Are the values '1', '01', '001' and '0001' considered to be unique, legal values or are they considered to be duplicates?

If the leading zero is not meaningful in your table, and is just there for formatting, then store the data as a number and format with leading zeros as needed for display purposes.

You can use the Format() function to do your formatting, as in this example query:

SELECT Format(number_field, "000000") AS number_with_leading_zeroes
FROM YourTable;

Also, number storage and indexing in all database engines I know of are more efficient than text storage and indexing, so with large data sets (100s of thousands of records and more), the performance drag of using text data type for numeric data can be quite large.

Last of all, if you need to do calculations on the data, you want them to be stored as numbers.

The key is to start from how the data is going to be used and choose your data type accordingly. One should worry about formatting only at presentation time (in forms and reports).

Appearance should never drive the choice of data types in the fields in your table.