Home » Posts tagged 'Number'

Tag Archives: Number

MS Access: Data Types Summary

Now that we have our first tables created in our database, it’s now time to talk about Data Types. Every field in a table has a data type which defines how Access will handle that field, as well as the type of data that can go into it.

You can easily see these data types by going into design view on one of your tables.  In the address table, for example, we have Number, Text, and Date data types.
AccessDataTypeExample

Please visit this page to view a list of all primary Access Data Types.


Data Type Limits

To show how these data types control the behavior and type of data that can go into each field…

  1. Go into “View” mode on your table
  2. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  3. Hit Enter to exit the field

AccessDataType01AccessDataType02

The zero is now gone. Zeros prior to a set of numbers are called leading zeros and the data type of “Number” does not support this format; which, technically makes sense, right? Numbers are shown as 12345, 1000, 9999.


I need leading zeros

Let’s say that you are working with data that has leading zeros, or maybe you wanted to add them to your five digit EMPLID to make the ID seven digits long. This can be done by first changing the data type of your EMPLID to Text.

Text is a data type that is used for alpha characters (ABCD)  as well as a combination of alpha and numeric characters. The Text data type also only supports up to 255 characters.

  1. Go into design mode on your Employee_Addresses Table
  2. Choose “Text” from the EMPLID data type drop-down
  3. Go into “View” mode on your table
  4. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  5. Hit Enter to exit the field

AccessDataType03
AccessDataType04

We now have our leading zero in our EMPLID!


To Note:

It’s important to note that the data in these tables came from Excel.  Access auto-identified the data types when we pasted it into the table. Even if we had pre-formatted our EMPLID field to be Text in Excel, Access would still have made this field a number.