Leading Zeros
By Richard Rost
3 years ago
Display Leading Zeros on a Number in Access
In this Microsoft Access beginner tutorial I'm going to teach you how to keep the leading zeros in front of a number such as a ZIP code, social security number, or any other type of data.
Mia from Annapolis, Maryland (a Gold member) asks: I'm new to Microsoft Access and I have ZIP codes from some of my customers that start with zero. Whenever I display them on a mailing label I'm only seeing four digits. I know in Excel you can type a single quote to keep that leading zero. How do I do this in Access?
Prerequisites
Links
Format: https://599cd.com/Format
Format Currency: https://599cd.com/FormatCurrency
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, leading zeros, leading zeroes, zeros at front of number
Subscribe to Leading Zeros
Get notifications when this page is updated
Intro In this video, we talk about handling leading zeros in Microsoft Access, focusing on how to ensure values like zip codes display correctly, especially when starting with a zero. We discuss why it's important to store certain data, like zip codes and postal codes, as text instead of numbers, and show how to use formatting options if you choose to store them as numbers. We'll also compare how Excel and Access treat leading zeros and cover best practices for data storage in Access tables.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about leading zeros in front of your values in Microsoft Access. Today's question comes from Mia from Annapolis, Maryland, one of my Gold members. Mia says, I am new to Microsoft Access and I have zip codes from some of my customers that start with zero. Whenever I display them on a mailing label, I am only seeing four digits. I know in Excel you can type in a single quote to keep that leading zero. How do I do this in Access?
Mia, I can always tell the people who send me questions that have not watched my Access Beginner One class yet. The first thing I am going to suggest you do is go watch this. I know it is four hours long, but I cover all the basics, including the answer to this question. I know a lot of people think they do not need it because they have been using Access for a while, or in your case, if you are brand new to it, go watch it. Please watch this. I cover all the fundamentals. It is free. It is on my YouTube channel. It is on my website. Go watch it.
I do have a scaled down version available as well. On my YouTube channel, I have a 30-minute how to use Access video, which is kind of like a jump starter because some people have told me they do not want to sit through four hours of video. Go watch this one if you do not want to watch the full one. I will put a link to both of these down below.
Now, what Mia was saying was if you are familiar with Excel, if you are typing in some zip codes, let us say 14226, 33909, 90210, and then you have 01234, you lose that leading zero because Excel is storing this data as numbers. With a number, you do not put a leading zero on there. So you can force Excel to treat this as text by starting out with a single quote, 01234, and then it will leave it there. Notice it lines up on the left side of the cell. Right there, that should tell you that is text. It is now a text value.
The thing with Excel is you can type whatever you want anywhere in a spreadsheet and Excel for the most part does not care. With Microsoft Access, however, you have to be a little more detailed with what goes where. You have to define your fields, your columns, your fields, and you can set rules as opposed to Excel where you can just type whatever you want.
Let us go back over to Access now. Here is my TechHelp free template. This is a free database. You can download a copy from my website if you want to and I have also got videos to teach you how I built all of this. I will put links down below. If you look in my customer table, I have a zip code right there and you can see that it handles leading zeros just fine.
Why is that? Let us take a look in Design View. Notice I am storing zip code as text, short text. Why is that? I talk about this a lot in my Access Beginner One class. Basically, if it is a value that you are ever going to do math on, or you are going to add up a bunch of zip codes, you are going to find the average of a bunch of zip codes, then store it as a number. That is fine. If not, if you are not going to do math on it, store it as text. Text is easier to deal with.
Especially if you are dealing with people from other countries, you get some customers from Canada or the UK, they have got letters in their postal codes. A zip code that is only numeric cannot store that data. So making your zip code or your postal code allow text works and is easier to deal with too.
But in case you do want to keep this as a number and some people do, let me show you a workaround here.
Let us delete that zip code. Let me come down to the end here and I will make a new zip code field. We will call it zip code. We will leave this as a number. Save that. Now, come over here and I will put some zip codes in here. 14226, that is my old zip code back in Amherst, New York. 33909 is where I am at now in Cape Coral, Florida. 90210, I have never been. And then 01234. There goes my zero, bye bye.
Now how do I keep it so I can still see that zero in here? We can use a format. Back to Design View, click on the zip code, come down here where it says Format. Type in 00000 (five zeros). What that says is a digit goes here and if there is no digit, put a zero there.
I just did a video last week, I think, on formatting currency. It is the same thing with currency. I will put a link to that and my format videos down below.
Now, once you type this in here, you get this little pop-up that says "Update the format everywhere zip code is used." That means it will go through and propagate this format throughout your forms, queries, reports, and so on. If you want to change all the things that are already in your database, you can do that. I am going to skip that for now since it is a brand new field. That means at this point forward, whenever I make a new form or a new report, it will pull in this format automatically. This is just propagated in case you change it. That is in newer versions of Access.
Save it and come back over here and take a peek now, and there we go. We can see we have got our leading zero on that one now because I told Access to always show five digits there.
Again, my recommendation for zip codes, Social Security numbers, anything you are not doing math on, use text. Use short text. I talk about this in a lot more detail in my Access Beginner Level One class. It is free. It is on my YouTube channel. It is on my website. Go watch it. Even if you think you do not need it, you will benefit from it. Take a Sunday when you have some time and just put it on the YouTubes and watch.
There you go. There is your TechHelp video for today. I hope you learned something. Live long and prosper. I will see you next time.
Oh, and a little trivia. I actually had to look this up myself. There is "zeroes" and then there is "zeros." Technically, I guess according to the sources I have read online, both are correct. You can use either one. In the UK and the US, "zeros" without the E is the preferred spelling. "Zeroes" with an E usually is if you are saying like, the pilot zeroed in on his target, he zeros in. Either one is fine. So if anyone complains, just tell them no.
Okay, bye.Quiz Q1. Why do zip codes beginning with zero often lose their leading zero in Excel?
A. Excel treats zip codes as numbers and removes leading zeros by default
B. Excel formats all zip codes as dates
C. Excel only allows five-digit numbers to start with one
D. Excel converts zip codes to text automatically
Q2. What is the standard way to keep a leading zero when entering data in Excel?
A. Precede the value with a single quote
B. Enter a space before the number
C. Format the cell as a number with decimals
D. Use asterisk (*) before the value
Q3. In Microsoft Access, why is it often better to store zip codes as short text rather than as numbers?
A. Because you usually do not perform math operations on zip codes
B. Because Access does not allow numbers as field types for addresses
C. Because short text takes less storage space
D. Because short text fields can automatically recognize US states
Q4. What happens if you store zip codes as numbers in Access and enter a code like 01234?
A. The leading zero is removed and only 1234 will be stored
B. Access will automatically convert it to text
C. Access will display an error and not save the value
D. Access will store 01234 but not allow it to be used in forms
Q5. If you need to force Access to show five digits in a number field, including leading zeros, which format should you use?
A. 00000
B. #####
C. 99999
D. 0000#
Q6. When you apply a format like 00000 to a zip code number field in Access, what does it do?
A. It displays leading zeros if there are fewer than five digits
B. It converts the number to currency
C. It counts the number of digits in each value
D. It prevents any zeros from being entered
Q7. What is a strong reason to use text fields for postal codes in an Access database that stores customer data from multiple countries?
A. Some countries have postal codes with letters as well as numbers
B. Numbers are always required in international postal codes
C. Text fields do not support special characters
D. Numeric fields offer better sorting for international codes
Q8. In Access, what will happen if you apply a format to a field and select 'Update the format everywhere [field] is used'?
A. The format will be updated in forms, queries, and reports that use that field
B. Only the current table will be affected
C. All fields in the database will be changed to that format
D. Access will give an error and not update anything
Q9. According to the video, what is the instructor's general recommendation for storing things like zip codes and Social Security numbers?
A. Store them as short text fields
B. Store them as number fields
C. Store them as date/time fields
D. Store them as currency fields
Q10. What is a key conceptual difference between Excel and Access regarding how you store and manage data?
A. In Excel, you can type anything anywhere, but Access requires predefined fields and data types
B. Excel requires you to define field types before entering data
C. Access allows you to change data types on the fly, unlike Excel
D. In Access, you cannot format numbers to show leading zeros
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on handling leading zeros in Microsoft Access, particularly with values like zip codes.
This is a common question. Many of you have probably run into something like this, especially if you are new to Access and are used to working with Excel. When typing zip codes such as 01234 in Excel, the software removes that leading zero if you save the value as a number. In Excel, you can force a leading zero to display by entering a single quote before the number, making it text. Excel is pretty flexible about what type of data you enter in any cell.
Access, however, operates differently. Access requires you to define your table's fields with specific data types, and this affects how data is stored and displayed. For example, in my free TechHelp template database, if you look at the design of the customer table, you will see that the zip code field is set up as short text, not as a number. This is intentional and important. I always recommend storing zip codes and similar values as text fields because you are unlikely to do any mathematical operations with them, such as adding up zip codes or calculating their average. If you need to perform calculations, then storing the data as numbers makes sense. Otherwise, stick with text for simplicity.
Storing zip codes as text is even more important when dealing with international data. Postal codes from places like Canada or the UK can contain both numbers and letters, so a numeric field would be too restrictive. Defining the zip code field as short text keeps your database flexible and avoids issues with missing leading zeros.
But what if, for some reason, you need or want to keep the zip code as a number? Maybe you inherited a database set up that way, or someone on your team insisted on it. If you store zip codes as numbers in Access and enter a value like 01234, Access will drop the leading zero by default. This is because numbers are not supposed to begin with zeros, according to how Access handles numeric values.
There is a solution for this. You can set a custom format for that field. If you switch the field to Design View and look for the Format property, you can type in five zeros (00000). This tells Access to always display five digits. If the number is shorter than five digits, Access will fill in missing places with leading zeros so 1234 will appear as 01234. If you ever need to apply this format everywhere in your database, Access will prompt you to update all related forms, reports, and so on. You can apply that update, but if you are only just setting up the field, skipping it is fine too.
Once this format is in place, anytime you view or print that field, your zip codes will keep their leading zeros, even if they are saved as numbers. Just remember, this is a workaround. My recommendation remains: for anything you are not going to perform calculations on, such as zip codes or Social Security numbers, you should use short text fields.
If you are still new to Access or coming from an Excel background, I highly recommend watching my Access Beginner Level One class. It covers these fundamental concepts in much more detail and will help you avoid common mistakes. This class is free and available on both my YouTube channel and my website. If you are looking for a faster introduction, there is a 30-minute version as well. Both links are available below.
And just a little side trivia: both 'zeroes' and 'zeros' are correct spellings. 'Zeros' (without the E) is preferred in the US and UK, while 'zeroes' is also acceptable in some contexts, mostly as a verb.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Storing zip codes with leading zeros in Access
Difference between text and number fields for zip codes
Setting field type to short text for zip codes
Viewing and editing field properties in Design View
Handling international postal codes with text fields
Creating a zip code field as a number
Formatting number fields to display leading zeros
Using custom format strings like 00000 for zip codes
Propagating field format changes in Access
Recommendation to store zip codes as text not numbers |
