Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Expert 18

Next: Access Expert 20 >

Access Expert Level 19

Expert Microsoft Access Tutorial - 1 Hour, 40 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 18 left off. In this class we will focus on Conditional Formatting and Exporting our data into other formats. Topics include:
 
  - Conditional Formatting
  - Advanced Conditional Expressions
  - Exporting to Microsoft Excel
  - Exporting to Microsoft Word
  - Microsoft Word Mail Merge
  - Exporting Text Files
  - Fixed Width vs. Delimited Text
  - Exporting to Other Databases
  - Exporting HTML Files for the Web

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 19
Description: Access Expert Level 19
Versions: Recorded with Access 2013. Also use with 2007 and 2010. Access 2003 users should get Access 308 for the lessons on exporting, and Access 310 for the lessons on conditional formatting.
Pre-Requisites: Access Expert Level 18 strongly recommended
Running Time: 1 Hour, 40 Minutes
Cost: $24.99


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."

conditional formatting

 

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.

conditional formatting

 

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.

expression is

 

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.

expression is

 

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.

disable changes

 

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."

 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.

data bars

 

Next we'll learn how to export data from our database into different formats. We'll begin by learning how to export to Excel.

export to excel

 

We'll discuss the different types of Excel file formats, and see how to get our data into an Excel spreadsheet.

export to excel

 

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.

customer export query

 

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.

export mailing labels to word

 

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 mass mailings.

mail merge

 

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.

mail merge

 

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.

export text

 

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 SQL Server.

export to xml

 

Finally, we'll learn how to export our data in standard HTML files that can be uploaded and used directly in your web sites.

export to html

 

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 19

00. Intro (6:15)

01. 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
Conflicting Rules
Order of Applied Rules
Using Functions like Date() in Conditions
Formatting Date Values
Formatting Text Values Exact
Find Text Inside of Field
Expression Is
InStr Function
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 Paid
Compare to Other Records
Data Bars
Lowest, Highest value
Numbers, Percentages
Max 50 Conditions

02. Exporting 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
Export selected records
Copy and Paste from Access to Excel
Copy Without Column Headers (Field Names)
Add an Export Checkbox to Table
Export Specific Records Query
Hide the Export Field
Save Export Steps
Create an Outlook Task with Reminder
Saved Exports
Manage Data Tasks
Run Saved Export Routine
03. Exporting to Word (15:05)
Export Mailing Labels
Export RTF File to Word
Mail Merge Access Data to Word Document
Word Merge
Microsoft 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 Word
Insert Address Block
Insert Merge Field
Preview Your Letters
Word Doc Automatically Updates from Table
Copy and Paste Table Data to Word

04. Exporting Text Files (10:14)
Export Text File
Export Windows Text
MS-DOS Text
Notepad Application
Basic Access Text Export is Garbage
Export Without Formatting
Export Text Wizard
Fixed Width vs. Delimited Text
Changing Width of Field
Field Delimiter
Text Qualifier
Date Order
Date Delimiter
Time Delimiter
Four Digit Years
Leading Zeros in Dates
Decimal Symbol
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 Discussion
Export to SQL Server, Brief Discussion
Export to HTML Document
Formatted v. Unformatted HTML
Coming Up

06. Review (5:32)

 


 
Keywords: Crosstab Query, Find Duplicates, Find Unmatched, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, conditional formatting, expression is, instr, export to excel, create an outlook task, export to word, word merge, microsoft word mail merge wizard, export text files, fixed width, delimited text, export to access, export html
 
 

Student Interaction: Microsoft Access Expert 19

Richard on 3/17/2014:  Microsoft Access Expert Level 19 is 1 hour, 40 minutes long. In this class we are continuing our work with Crosstab Queries. This class focuses primarily on advanced Conditional Formatting and Exporting Data from your Access Database to other formats, including Excel, Word (including Mail Merge), Text Files, Other Databases, XML, HTML, and more. Topics include: - Conditional Formatting - Advanced Conditional Expressions - Exporting to Microsoft Excel - Exporting to Microsoft Word - Microsoft Word Mail Merge - Exporting Text Files - Fixed Width vs. Delimited Text - Exporting to Other Databases - Exporting HTML Files for the Web Click here for more information on Access Expert Level 19, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 18.
Lucas Langerak on 3/19/2014: Looking forward to the remote data entry information. I should really be able to put that to good use.
AccessJunkie on 5/8/2014: Richard,
your lessons are addictive!!! thank you.
in your conditional formating, is there a way to create a blinking cell if, for example, a date gets out of the desired range??

thanks,

aj

Reply from Richard Rost:

Not without a timer event. Conditional formatting alone can't do that.


Hassan Abadi on 6/11/2014: Hi Richard:
What is the correct conditional formatting syntax to show the lowest price item in a form text box?

Thanks
Hassan

Brian Farley on 10/28/2014: Worth noting to prevent hair pulling out. Your field must have a background in order for the conditional formatting to change the format. If it's set to Transparent, the field will not change format until you click in it...

-signed the recently became bald guy

Chris Thompson on 3/19/2015: Access 2013 Expert 19 chapter 2 ~TS 15:00, we create an export. While I know we are presently avoiding VBA code, my question is - Once created, are we able to programmatically execute these saved exports from Access?

Reply from Alex Hedley:

Yep...
Are you wanting to know how?

vicki Hudson on 5/1/2015: Does the conditional formatting work with a combo box when it is automatically filled from another field?

Reply from Alex Hedley:

Why not give it a go and see :)

If it doesn't add a Me.Recalc

vicki Hudson on 5/3/2015: I was trying to have a field highlight on a report, only if a combo box on a form had a certain criteria. I can bold and underline it.

Reply from Alex Hedley:

How are you Bold/Underlining it at the moment?

You could look into either the OnFormat Event or Conditional Formatting.

If [CONDITION] Then
Me.[ControlName].BackColor = RGB(255,0,0)
Else
Me.[ControlName].BackColor = RGB(0,0,0)
End If

This MS Article maybe of use.

vicki H on 5/3/2015: I am using the conditional formatting. I am not using code I had to change the background color to Automatic/Background 1 before it would highlight. On to the next. Thanks Alex

Reply from Alex Hedley:

The joys of Themes, glad you sorted it out and thanks for sharing.

Stephen Orlans on 9/12/2015: Hi Richard I would like to mail merge using the current record's name and address only into a preformated word template but it seems that you can only mail merge a list and deselect the records you don't want. Can you merge a single record automatically? Thanks Stephen

Reply from Alex Hedley:

You could make a Query of 1 Record and merge that.

Brian Jensen on 1/9/2016: Is it possible to conditionally format a control on a form based on value in a query that isn't linked in the control source? I want a name to change color in one form if the person is away on a trip based on records from another table.

Reply from Alex Hedley:

You could try using a DLOOKUP in your Expression to get a value from another Table/Query and check that against your condition

Trond L on 1/26/2016: When choosing "Field has focus", is it possible to mark an entire line yellow, not just a single field at a time? (yellow or another color, of course)

Reply from Alex Hedley:

See this forum post.

Katheryn Hartig on 3/31/2016: Richard - These lessons are amazing! Keep 'em coming! I want to give my users the ability to select names from a list on a form and then hit a button that opens WordMailMerge and feeds the selected names to Word for merge. Do you cover that? Thanks!

Reply from Alex Hedley:

This course explains the Mail Merge, just make sure you have a query based on the records you want before following the steps in Word.

Katheryn Hartig on 3/31/2016: Also, I want to give my users a menu to select a variety of criteria and then return a recordset (e.g., find by discipline and date range and ...). Can this be done w/o programming? If so, which lesson(s)? Thanks!

Reply from Alex Hedley:

There's a whole Seminar on Search, check out the outline.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP