Microsoft Access 2021: Field Properties

Name: Microsoft Access 2021: Field Properties
Author: John Varan

Learn what field properties are and how to use them to improve your database design.

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

Hello. Welcome to Knowledge City's course on Microsoft Access 2021 Intermediate. My name is John Veron, and in these lessons, you will learn about field properties. In this lesson, I will show you about choosing the correct data type. As we've seen before, each field has a list of data types to choose from. Choosing the correct one is integral to having a good database design, and can also make a big difference in how easy your application is to use and how much work it takes to maintain. I'm going to go through the most important data types and explain what types of data they're best used for, and I'll also discuss some information commonly stored in databases and talk about what data type is best used to store them. Let me just create a table here we can use as a reference. Let's start with the string data type. Which in Microsoft Access is called either short text or long text. Short text can hold a maximum of 255 characters, while long text can hold up to 65,536. In general, always use short text unless you're sure you need more than 255 characters, because a long text data type is handled differently internal to access, which can lead to slower performance or can require some special considerations. Text is the most general data type, meaning you can technically store anything in there, numbers, dates, currency, or whatever else you can think of. The problem is that no matter what you store in it, access won't see it as anything other than text. This means if you store numbers in here, you won't be able to compare them like normal numbers or perform math on them. If you store dates, you won't be able to check which dates come before others, so always use one of the other more specific data types whenever possible and only use a text type when nothing else will work. However, some pieces of data can look like a different data type but still fit best as text. A good example could be zip code. On the surface, a zip code looks just like a number, so you might be tempted to use a number data type. However, zip codes can contain a hyphen and you won't be able to put that in for a number data type. Therefore, it's often best to store zip codes as text. Besides, you usually won't have to perform math or comparisons on zip codes anyway. The number data type is probably the next most commonly used data type. This is best used for things that are just a number with no other parts to them. This one behaves a little differently from the other data types because it really depends on the field size property. That's this one right here. Changing the field size will essentially make this behave like a different data type. The size is byte, integer, and long integer will all treat the number as an integer, meaning they are round, whole numbers without a decimal portion. The difference between them is the maximum number they can store. For most purposes, integer is a good default size. Single, double, and decimal are all numbers that can store fractional decimal portions. Similar to the integers, the difference between these is the maximum number and precision that they can store. These could be used to store currency, but there is actually a data type that will work better for that. The currency data type is designed for storing money. It takes less storage space than the decimal data types, allowing only up to four decimal places. But that is more than enough for currency. It will also automatically add the currency symbol based on your locale and your operating system. Date time is exactly what it sounds like and is made for storing dates, times, or a combination of both. Dates and times stored in this data type can be compared and sorted, which you would be unable to do if they were stored as text. Yes, no is a special data type that can only have two possible values, yes and no, or sometimes referred to as true and false or one and zero. Other databases may call this type something like bool, boolean, or bit. They are all the same thing. This data type is great for storing any sort of flag, which is a toggle that can only have two possible values. Things such as whether a record is active or not. Note that if you combine this data type with making it not required, then the column will allow three possible values, yes, no, and no. So if you need a flag with three possible states, this is how you would do it. Hyperlink is a data type, which is just a special case of the text data type. You could put hyperlinks into either type, but if you use hyperlink, then access will automatically let you click on the link to open it. So you can choose text or hyperlink depending on whether you want users to be able to click the link or not. Attachment and OLE object are special data types that you won't normally need, but they are designed to store entire other files or application objects directly in your database. In general, unless you have a specific need to do this, it is best to avoid doing so. Hopefully, this gives you a good understanding of the main data types and to best use them effectively. Thanks for watching. Stay tuned for the next lesson where I will show you field formats.

Intermediate Provider : John Varan 5 Lessons · 23m in English

Course Description

Choosing the right data type for each field in your table can make all the difference when it comes to having a well-functioning database. Being familiar with the different data types allows you to pick the best one to suit your needs and maximize the efficiency of your data storage. You can also ensure the best data entry possible by providing immediate feedback to users and by knowing how to reject invalid data. To learn these skills, the first step is exploring field properties and their functions.

In this module, you will learn about the different data types available in Microsoft Access and how to choose the best one for your data. We will go over how to format different user controls that can be used both on forms and in table fields. Finally, you will learn about validation rules and how to best use them to maintain your data integrity. Rejecting invalid data will help maintain the data integrity of your database and reduce errors from users.

What You'll Learn

Choose optimal data types for each field in your table
Format user controls on forms and in table fields
Create validation rules to reject invalid data
Add and use file attachments in your database
Maximize data storage efficiency by selecting the best data type

Key Takeaways

Choosing the right data type for each field helps create a well-functioning, efficient database.
Knowing the different data types lets you pick the best one for your needs and maximize data storage efficiency.
Providing immediate feedback and rejecting invalid data improves data entry and maintains data integrity.
Validation rules help maintain data integrity and reduce errors from users.
User controls can be formatted both on forms and in table fields.

Frequently Asked Questions

What does this Microsoft Access 2021 module cover?

It covers the different data types available in Microsoft Access and how to choose the best one, how to format user controls on forms and in table fields, validation rules for maintaining data integrity, and how to add and use file attachments.

What will I be able to do after completing this course?

You will be able to use optimal data types, format controls on forms and in fields, create validation rules, and add and use file attachments.

How does this course help maintain data integrity?

It teaches validation rules and how to best use them to reject invalid data, which maintains the data integrity of your database and reduces errors from users.

What skills does this course focus on?

It focuses on skills including Microsoft Access, Access query languages, data access, form fields, Microsoft Data Access Components, and Microsoft Forms.