Today we will
begin by learning all about Conditional Formatting.
This is where you can change the format (font, color, etc.) of data
based on the value of the data. We'll start by learning how you can
create simple rules, such as "change this field red if the value is
greater than or equal to 500."
Then we'll learn about more complex rules involving multiple
formats. For example, show the value in red if it's under $20 and yellow
if it's over $500.
Next we'll learn about the very powerful Conditional Formatting
Expressions. This is where you can set a format based on any
formula or function you want, such as changing the color of the
customer's name to yellow if it includes the string "Inc" within it.
You can even use expressions to change different
fields. For example, set the color of the entire row to yellow if the
order total is over $500.
You can use conditional formatting to disable changes
to a record if a particular value is set. For example, don't let the
user make any changes to an order once the "Is Paid" checkbox is set to
yes. In this example we just locked the customer selection combo box,
but you can apply it to all of the text fields if you'd like.
You can use the Field Has Focus rule to set the format
of the field that the user is currently on (clicked or tabbed to). This
is nice for highlighting which field "has focus."
You can use Data Bars to visually indicate the highest
and lowest values in a range of values. For example, here we've used
Data Bars to show the highest sales and lowest sales. The higher the
order total, the more "red" the field appears.
Next we'll learn how to export data from our database into different
formats. We'll begin by learning how to export to Excel.
We'll discuss the different types of Excel file formats,
and see how to get our data into an Excel spreadsheet.
You might not want to export ALL of the records from a table, so next
we'll make a Customer Export Query so we can go through
our list of customers and pick which ones we want to export to Excel.
Next up, exporting to Microsoft Word. We'll see how you can take those
mailing labels we created in an earlier class and send them straight to
Word. This way you can edit them individually before printing them, or
you can send them to someone else to be printed.
We will learn how to use a powerful feature called Mail Merge in
Microsoft Word. This will allow us to use data from our Access database
and merge it into document (such as letter or envelopes) in Word to send
We'll learn how to use Merge Fields to format the
document exactly as we want it, by putting the data fields from our
database exactly where we want them in the document.
Next we will learn about Exporting Text Files in
various formats. We'll learn about Fixed Width and Delimited Text, field
delimiters, text qualifiers, formats for dates, and much more. We'll
learn how to save our export specifications to be used later.
Finally, we'll learn how to export to other databases.
We'll export our tables to another Access database, export to XML, and
we'll discuss exporting to Sharepoint and other ODBC sources, such as
Finally, we'll learn how to export our data in standard HTML
files that can be uploaded and used directly in your web sites.
This is the 19th class in the Access Expert series. There
is a lot of great material in this class. Conditional Formatting
will add a look of professionalism to your database, and
knowing how to export data into various other formats
will help you share your data with the world. Of
course, if you have any questions about whether or not this class is
for you, please contact me.
Complete Outline - Access Expert Level
00. Intro (6:15)
Conditional Formatting (28:13)
Format large orders green
Conditional Formatting Rules Manager
New Formatting Rule
Check values in the current record or
use an expression
Field Value Is
Greater than, Less than, etc.
Highlight orders over $500 in green
Add Views to Quick Access Toolbar
Order of Applied Rules
Using Functions like Date() in
Formatting Date Values
Formatting Text Values Exact
Find Text Inside of Field
Format Customers with "Inc" in name
Apply to Multiple Fields at Once
Format Entire Row
Format if Field Has Focus
Disable Changes if Order Marked Paid
Conditional Format Subform Value
Disable Editing Products on Order if
Compare to Other Records
Lowest, Highest value
Max 50 Conditions
to Excel (16:37)
Reasons to Export Data
Export Table to Excel
Different Versions of Excel Data
Export data with formatting and layout
Open the destination file after the
Export selected records
Copy and Paste from Access to Excel
Copy Without Column Headers (Field
Add an Export Checkbox to Table
Export Specific Records Query
Hide the Export Field
Save Export Steps
Create an Outlook Task with Reminder
Manage Data Tasks
Run Saved Export Routine
03. Exporting to Word (15:05)
Export Mailing Labels
Export RTF File to Word
Merge Access Data to Word Document
Word Mail Merge Wizard
Link your data to an existing
Microsoft Word document
Create a new document and then link
the data to it
Mail Merge Wizard
Edit Recipient List in
Insert Address Block
Insert Merge Field
Word Doc Automatically Updates from Table
Copy and Paste Table Data to Word
04. Exporting Text
Export Text File
Export Windows Text
Basic Access Text Export is Garbage
Export Without Formatting
Export Text Wizard
vs. Delimited Text
Changing Width of Field
Four Digit Years
Leading Zeros in Dates
Save Export Specification
Include Field Names in First Row
05. Export Misc (18:23)
Export to XML
Export to Another Access Database
Database must be closed
Access needs exclusive read/write
Queries Export as Queries NOT Tables
Copy Multiple Tables
Export to Sharepoint, Brief Discussion
Export to ODBC, Brief
Export to SQL Server, Brief Discussion
to HTML Document
Formatted v. Unformatted HTML
06. Review (5:32)