Introduction

Welcome! Conditional Formatting & Export

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 19. In this course we will start by learning about conditional formatting, which lets you change how your data looks based on its values, and discuss some of the advanced rules you can apply. We will then move into exporting data from Access to other formats, including Excel, Word, and text files, as well as various export options. We will also cover related topics such as mail merge, different text file types, and exporting to other database files. Details about additional seminars, support forums, and course structure are also provided.

Navigation

Prev: X19
Next: Lesson 01

Keywords

Access Expert, conditional formatting, export to Excel, export to Word, mail merge, export text files, text file formats, export database files, crosstab queries, find duplicates, unmatched queries, query properties, event programming, macros, Visual Basi

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 19 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to begin by learning conditional formatting, which is changing the format or the appearance of the data in your database based on the values of that data. I saved this for the expert series because there are some advanced rules you can apply to conditional formatting that really make your databases look good. Conditional formatting is more than just a simple beginner topic.

Then, once we've finished conditional formatting, we'll move into exporting data from your database into other formats. We'll learn how to export to Excel, to a Word document, how to perform a mail merge using Microsoft Word, how to export to text files, and the different types of text file formats. We will also learn how to export to other database files.

This class follows Access Expert Level 18. The topics in this class can really stand alone. You do not have to have taken the previous classes in the series. However, I do strongly recommend you take all of my classes in order and do not jump around a lot.

Access Expert 18 covers crosstab queries, find duplicates and unmatched queries, and query properties.

Today's class is recorded with Access 2013. If you have Access 2007 or 2010, you should be able to follow along with this lesson. There are some minor differences with the menus and with conditional formatting, but you should be able to figure them out. It is not that different.

If you have Access 2003 or earlier, I cover exporting in Access 308 and conditional formatting in Access 310.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels: level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you master the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to learn about conditional formatting, which is changing the format of a field based on its value or the value of other fields.

In the next couple of lessons, we are going to learn how to export data from our database into other formats.

In lesson two, we will learn how to export from Access to an Excel file.

In lesson three, we are going to learn how to export data from Access into Microsoft Word.

In lesson four, we are going to learn how to export text files from our Access database.

In lesson five, we are going to talk about a whole bunch of export topics that we have not covered yet.

Quiz

Q1. What is conditional formatting in Microsoft Access?
A. Changing the format or appearance of data based on its value
B. Writing complex queries to filter data
C. Encrypting sensitive fields in your database
D. Importing data from other sources

Q2. Why is conditional formatting saved for the expert series in the course?
A. Because it has advanced rules that make databases look good
B. It is too easy for beginners
C. It can only be done with VBA
D. It is only available in Access 2019 and later

Q3. Which of the following is NOT one of the export formats discussed in this lesson?
A. Export to Excel
B. Export to Word and Mail Merge
C. Export to PowerPoint
D. Export to text files

Q4. Is it necessary to have completed all of the previous Expert Level classes before taking Expert Level 19?
A. No, each class can stand alone but it is recommended to take all of them in order
B. Yes, it is mandatory to complete all previous classes
C. Only if you do not use Access 2013
D. Only if you want to export data

Q5. What topics did Access Expert Level 18 cover?
A. Crosstab queries, find duplicates and unmatched queries, and query properties
B. Event programming and macros
C. Building web-based databases
D. Creating forms that look like calendars

Q6. What is suggested if you get stuck or do not understand something in the lesson?
A. Watch the video again from the beginning or use the student forums for help
B. Skip the lesson and move to the next
C. Try to figure it out without any help
D. Contact Microsoft Support directly

Q7. Which Access versions does the lesson primarily use and support?
A. Access 2013, with minor differences noted for 2007 and 2010
B. Access 2000 only
C. Only Access 2016 and newer
D. Only Access for Mac

Q8. What is the best way to get the most out of this course according to the instructor?
A. First watch the lesson, then replay and follow along step by step
B. Try to apply lessons immediately to your own projects
C. Memorize the transcript
D. Only download the finished database

Q9. What resource does the instructor recommend for finding help and discussing lessons?
A. The student forums on the AccessLearningZone.com website
B. Microsoft Office official forums
C. Social media groups
D. Emailing other students directly

Q10. What does the instructor encourage students to do with his sample database?
A. Download and tear it apart to learn, just like Northwind Traders
B. Never download it, to enforce their own learning
C. Submit it as their own for assignments
D. Share it publicly outside the classroom

Answers: 1-A; 2-A; 3-C; 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 video from Access Learning Zone is Access Expert Level 19, and I am your instructor, Richard Rost.

In this class, I'll be introducing you to conditional formatting. This feature lets you change how your data looks based on the information in your tables or forms. While conditional formatting might seem like a simple concept, there are many advanced rules and techniques you can use to really enhance the appearance and usability of your Access databases. That's why I have included it in the expert series of courses, and not in the beginner classes.

After we finish this topic, I will show you how to export your data from Access into other formats. You will learn to export to Excel, Word documents, and even how to perform a mail merge in Word. I will also cover exporting to text files and discuss the various formats available, along with exporting to other types of database files.

This class is a direct follow-up to Access Expert Level 18. However, you do not have to take the previous class to understand the material in this one, since the topics covered here can stand on their own. That being said, I always recommend that you take my courses in the order they are presented, rather than skipping around.

For context, Access Expert 18 focused on crosstab queries, finding duplicate and unmatched records, and worked with query properties.

The material for today's class is recorded using Access 2013, but you should be able to follow along if you are using Access 2007 or 2010. There are a few minor menu differences and some slight changes to conditional formatting, but you should not have trouble keeping up. If you are using Access 2003 or an earlier version, I have separate lessons covering exporting data and conditional formatting in my Access 308 and 310 courses, respectively.

My curriculum is organized into different skill levels: beginner, expert, advanced, and developer. At the beginner level, you should become comfortable with all basic topics before moving up. Expert-level classes, like this one, build on that foundation. The advanced series moves into programming events and macros, and the developer classes focus on Visual Basic for Applications.

Each level is broken down into multiple classes, for example, level 1, level 2, level 3, and so on.

Along with my standard Access courses, I offer a variety of specialized seminars on specific topics. For example, you can learn about building web databases, creating calendar-style forms and reports, securing your databases, managing images and attachments, setting up work orders, service business management, tracking accounts payable, using SQL, or creating loan amortization schedules. More details about these are available on my website, accesslearningzone.com.

If you have questions about anything we discuss today, you are encouraged to bring them to the student forums. If you are watching this course in my online theater, the forum for each lesson appears adjacent to the video. You can read questions from other students, responses from me, and additional comments. I recommend looking through the forum at the beginning of each lesson and participating in the discussion as needed.

Even if you are not watching these classes on my website, you can still access the forums by visiting accesslearningzone.com/forums.

To get the most out of this class, I always suggest that you first watch each lesson all the way through without touching your computer. Then, go back to the start and follow along with your own copy of Access, recreating the projects step by step. Focus on duplicating the sample database from class until you really understand each new skill. Hold off on adding the lesson material into your personal projects until you have mastered the examples shown.

If you get stuck or have trouble understanding a concept, watch the video again from the beginning or post a question in the student forum. I'll do my best to help you.

The most important thing is to keep an open mind. Access can feel overwhelming in the beginning, but once you become familiar with how it works, you will see how easy and powerful it can be.

I highly recommend that you build the database shown in today's class while you follow the video lessons. If you prefer, you can also download a sample copy of my completed database file from my website at accesslearningzone.com/databases. Sometimes breaking apart a finished database is the fastest way to figure out how things work. One of the ways I learned Access was by exploring the Northwind Traders sample database that comes with Access. I always provide an example database for each of my classes on my website.

Let me give you a quick overview of what is included in today's class.

Lesson one covers conditional formatting and teaches you how to change the format of a field based on its value or the values in other fields.

The following lessons cover various methods for exporting data from Access to other programs and formats. In lesson two, you will learn to export from Access to Excel. In lesson three, you will see how to export to Microsoft Word. In lesson four, you will learn how to export text files from Access. Finally, in lesson five, I will discuss a collection of additional export-related topics that have not yet been addressed.

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

Conditional formatting in Microsoft Access
Advanced rules for conditional formatting
Exporting data from Access to Excel
Exporting data from Access to Word
Performing a mail merge with Microsoft Word
Exporting data to text files
Types of text file export formats
Exporting data to other database files

Article

Welcome to this tutorial on advanced Microsoft Access techniques. In this article, we will dive into the concept of conditional formatting, which allows you to change how data appears in your database based on its values, and then explore several methods to export data from your Access database into other formats such as Excel, Word, and text files.

Conditional formatting is a tool that lets you modify the appearance of your data in tables, queries, or especially forms and reports, depending on certain conditions. For example, you might want to highlight overdue invoices in red or draw attention to high sales values in bold. While conditional formatting can seem simple at first, it offers advanced rules that can greatly enhance the usability and look of your database.

To apply conditional formatting in Access, start by opening your form or report in Layout View or Design View. Click on the control you want to format, such as a text box. Then, go to the Format menu and choose Conditional Formatting. A dialog box appears with options to add new rules. For instance, if you want to highlight all order totals over $1,000, you would add a new rule with the condition "Field Value Is greater than 1000". After setting up the condition, choose a format like changing the font color to red or making the text bold. Click OK to apply the formatting. Now, whenever the value in that control meets your condition, the formatting changes automatically.

Beyond simple value checks, you can use expressions for more complex logic. For example, you can format a date field to appear in a different color if the date is older than today by using a rule like "Field Value Is less than Date()", which highlights any past dates.

Once you are comfortable with conditional formatting, exporting data to other programs is a key skill. Access allows you to send your data to Excel, Word, and various text file formats, making it easier to share and analyze information outside of your database.

Exporting to Excel is straightforward. With a table or query open, go to the External Data tab and choose the Excel option. Access will prompt you to select the destination file and any specific details, like whether to include formatting. After completing the export, your data will be available in an Excel file for further analysis or sharing.

Exporting to Word can be especially useful when you need to merge your database information into documents or letters. You can use the Export to Word feature directly, or perform a mail merge using Word. For a mail merge, you typically start in Word, choose to use an existing Access database as your data source, and then select the table or query containing the information you want. Word will guide you through inserting merge fields and generating personalized documents for each record in your database.

Exporting to text files is helpful if you want to import your data into another system or simply need a plain text copy. From the External Data tab, choose Text File. Access will ask for the destination and will guide you through a wizard where you can select between delimited and fixed-width formats. Delimited files, such as CSVs, use a character like a comma to separate fields, while fixed-width files align columns by character width.

In addition to these methods, Access can export to database file formats and other applications, depending on your needs. Sometimes you might want to automate exports or perform custom logic. You can use VBA (Visual Basic for Applications) to programmatically export data. For example, the following code exports a table named "CustomerT" to an Excel file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "CustomerT", "C:\Temp\CustomerExport.xlsx", True

This command tells Access to export the "CustomerT" table to an Excel file at the specified path, including the field names (the True at the end).

These export options make Access a versatile tool for working with data across systems. Conditional formatting and exporting are valuable skills that let you make your database both more visually effective and more connected to the other software tools you use.

As you practice these techniques, remember that building step by step and testing your results is the best way to learn. Set up sample tables or forms, apply formatting rules, and experiment with different export types to see how the data appears in the destination software. By mastering these features, you will make your Access databases both easier to use and much more powerful.