Using Numeric Fields in Microsoft Access

I’ve been using Microsoft Access for many years, and one of the things I come across over and over is issues that arise when folks use numeric fields and text fields incorrectly. This can cause all sorts of odd and annoying problems with your data tables. In this article, I want to give you some tips on how to use the right field type for the job.

About Real Numbers

No, it’s not some mathematical argument about real vs imaginary numbers, (apparently, it’s a thing). This is about how to use the numeric fields correctly in your data tables. Put another way, numbers aren’t always numbers. Just ask any Excel user with an inherited spreadsheet!

What I mean is, if you think about the way we use numbers in a list or a data table, some of those should actually be treated as text. The reason is due to the way Microsoft Access (and Excel) treats numbers. You see, when you enter a number into a table, Access will treat that as a real number, which means it can be used in calculations. For example, if I create a field to store Units in Stock, then I can use the number to work out if I need to reorder, or to calculate the total cost by multiplying it by the Price.

However, if I use a number field to store information like Post Code, or a credit card number, then I can run into problems.

Let’s check it out:

Setting up Numeric Fields

In the example below, I created a post code field, which is set as a text field.

This is because, although Australian Post codes are numeric, you’re never going to use them in a calculation, because it simply wouldn’t make sense to do so.

Let’s look at another example. In this case, I have a customer table for my 24 hour gym, where each customer is assigned a card with a six digit code that they can use to enter the gym.

It all starts well, but when I enter the number starting with zeros, as soon as I enter the record, they disappear, leaving me with a partial code that doesn’t match what’s on the membership card. Big problem!

After – this is not good!

Access removes the leading zeros, because it identifies them as being non-significant! You may have seen the same thing happen in Excel. In fact, I get quite a few support calls from my clients about this exact issue.

So, I need to make a change to the way my data is stored. Ideally, this should be changed during the testing phase of your database, since changing field types after adding data might be problematic. You’ve been warned!

All I need to do is to open the table in Design View, then use the Data Type drop down list to select a text option. In my example, I selected the Short Text data type, since this field will only contain six characters.

Once I save the table, I can go ahead and test it to make sure it works the way I want it to.

Quick Tip

When you’re working with Microsoft Access, a good idea is to ask yourself whenever you have a field with numbers in it, “Am I ever going to use this field for calculations?”