ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access Expert Level 25 Tutorial Comprehensive Function Guide, String Functions, Logical Functions
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
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 Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Expert 24

Next: Access Expert 26 >

Access Expert Level 25

Expert Microsoft Access Tutorial - 1 Hour, 33 Minutes
 
 
Access Expert 25 is Part 1 of our Comprehensive Guide to Access Functions. In today's class we will focus on String (Text) and Logical Functions. You will learn a bunch of new functions, and new tricks with some of the functions we've covered before. You will learn how to separate first, last, and middle names from a full name field, calculate overtime pay, and lots more. Topics include:
 
  - String Functions
  - Left, Right, Mid, InStr, InStrRev, Len
  - LCase, UCase, Trim, RTrim, LTrim

  - Replace, Str, CStr, StrComp, ASC, CHR
  - Space, String
  - Logical Functions
  - IsDate, IsNull, IsNumeric, IsError
  - NZ, VarType, TypeName

  - And, Or, Not, Xor, IIF, Nested IIF
  - Switch, Choose

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 25
Description: Access Expert Level 25
Versions: Recorded with Access 2013. Most of the material should work with all versions of Access. There have been some functions (such as Switch) which were added in 2007, but the majority of them go all the way back to the first versions of Access.
Pre-Requisites: Access Expert Level 24 recommended, as you should watch all of my classes in order. However, if you're skipping around, you should have at least completed the Beginner series and the first 3 or 4 levels of the Expert series where I start to cover functions.
Running Time: 1 Hour, 33 Minutes
Cost: $26.99


We have covered some of these functions in previous classes, however in this Comprehensive Guide to Access Functions we will cover those in a lot more detail, plus learn many new functions. Today we will be learning about String and Logical functions. We'll start out by learning how to use the Left, Right, and Mid functions to separate a phone number into its various parts: area code, prefix, and suffix.

separate phone number

 

Next we will learn how to use the InStr, InStrRev, and Len functions to pull apart the First Name and Last Name from a field where someone has given you them both together. We covered this concept briefly in Access Expert 14, but in this class I'll show you a much better method which will grab the last name even if there's a middle name or initial. Plus, later on we'll see how to separate a Middle Name as well.

separate first last name

 

We will next see how to use the UCase, LCase, and StrConv functions to change the case of a string, including Proper Case (converting "richard rost" to "Richard Rost", for example). We'll use the Trim, RTrim, and LTrim functions to remove blank spaces from around a string. We'll use the Replace function to replace one substring with another. For example, changing "St." to "Street" in an address field.

replace function

 

Next we'll learn how to use the Str and CStr functions to convert a number to a string (text) data type. We will use the StrComp to compare two strings together to determine if they are equal, or if one is less than or greater than the other (alphanumerically). We'll learn about ASCII codes, why they're useful, and how to use the ASC and CHR functions. We'll learn about the Space and String functions to repeat a space or any other character X number of times. This is great for padding text strings in list boxes or combo boxes.

space string

 

Next we turn our attention to Logical functions. We will use the IsDate, IsNull, IsNumeric, and IsError functions to determine if our values are any of those types. We'll use the NZ function to convert null values to zero (or any other value we want). We'll use the VarType and TypeName functions to evaluate what the specific data type of any value is. We'll learn about the logical operators and why they're useful: And, Or, Not, and Xor (eXclusive Or).

and or not xor access

 

We've covered the IIF function before, but we'll spend a lot more time with it in this class. We'll start by learning how to give a student a Pass/Fail grade.

pass fail grade iif

 

Then we'll nest a couple of IIF functions together along with the IsNull function to first check to see if the student is missing a grade. If so, give them an "N/A" to indicate an incomplete, otherwise give them a "Pass" or "Fail."

pass fail incomplete iif

 

Next we'll see how to use multiple IIF functions to give students a letter grade (A, B, C, D, F) however we'll see how nesting IIF functions can become cumbersome after you have this many conditions. A better function for this would be the Switch function which allows you to set simple condition/value pairs for a much cleaner solution.

switch function letter grades

 

We'll also learn how to use the Choose function to select from a list of options. For example, we'll set up a value for the customer's preferred shipping method: USPS, UPS, and FedEx, and we'll use the Choose function to display the proper result.

choose function

 

One of the questions I get asked frequently is how to calculate Overtime Pay for employees on a time sheet. In this lesson, I'll show you how to enter the total number of hours worked in a week, how to calculate regular and overtime hours, and then regular and overtime pay for each employee.

overtime pay timesheet iif

 

Finally, remember earlier when I promised I'd show you how to separate out Middle Names from a field where the user gives you "First Middle Last" all in the same name field? Well, now is the time. I'll show you the magic combination of a bunch of different functions to pull out the First Name, Last Name, and Middle Names from a combined text field.

first middle last name separate

 

This is the 25th class in the Access Expert series. This is just the first in my Comprehensive Function Guide series. If you're serious about building quality databases with Access, don't miss out on this course. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 25

00. Intro (5:44)

01. String Functions 1 (26:27)
Function Categories
Left()
Right()
Mid()
Separate Phone Number Parts
Area Code, Prefix, Suffix
InStr()
InStrRev()
Len()
Separate First and Last Names
Get Last Name even if Middle Name
UCase()
LCase()
StrConv()
Convert to Upper Case
Convert to Lower Case
Convert to Proper Case
Trim()
LTrim()
RTrim()
Remove Extra Spaces from String
Replace()
Replace One String with Another
String Concatenation &

02. String Functions 2 (14:51)
Str()
CStr()
Convert Number to String
StrComp()
String Comparison
Greater, Less, Equal, NULL
ASC()
CHR()
ASCII Code Table
Non-Standard Characters
Space()
Monospace Fonts
Kerning, Kerned Fonts
String()
Repeat a String X Times
03. Logical Functions 1 (14:23)
IsDate()
IsNull()
IsNumeric()
IsError()
NZ()
Replace NULL Value
VarType()
TypeName()
Determining Data Types
Data Type Names
AND, OR, NOT, XOR
Logical Operators

04. Logical Functions 2(26:33)
IIF()
Nested IIF()
Student Grades Pass/Fail
Student Letter Grades
Switch()
Choose()
Shipping Methods
Calculating Employee Overtime Pay
Separating First, Middle, Last Names

05. Review (5:44)

 


 
Keywords: Comprehensive Function Guide, String Functions, Logical Functions, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, Left, Right, Mid, InStr, InStrRev, Len, LCase, UCase, Trim, RTrim, LTrim, Replace, Str, CStr, StrComp, ASC, CHR, Space, String, IsDate, IsNull, IsNumeric, IsError, NZ, VarType, TypeName, And, Or, Not, Xor, IIF, Nested IIF, Switch, Choose
 
 

Student Interaction: Microsoft Access Expert 25

Richard on 9/21/2014:  textMicrosoft Access Expert Level 25 is 1 hour, 33 minutes long. This is the first class in my Comprehensive Guide to Access Functions series. You will learn a bunch of NEW functions, and new tricks with some of the functions we've covered before. You will learn how to separate first, last, and middle names from a full name field, calculate overtime pay, and lots more. Topics include: - String Functions - Left, Right, Mid, InStr, InStrRev, Len - LCase, UCase, Trim, RTrim, LTrim - Replace, Str, CStr, StrComp, ASC, CHR - Space, String - Logical Functions - IsDate, IsNull, IsNumeric, IsError - NZ, VarType, TypeName - And, Or, Not, Xor, IIF, Nested IIF - Switch, Choose Click here for more information on Access Expert Level 25, including a course outline, sample videos, and more. This course was recorded using Access 2013, but most of the functions covered are valid for all versions of Access. This class follows Expert Level 24. The next class in the series is Access Expert 26.
saher on 11/6/2014: thanks
Terry Hopper on 12/23/2014: I would be very interested in an payroll class as mentioned in Access 2013; Expert 25; Lesson 4.
Kenny Nelson on 2/5/2015: At 2:03, you mention VB functions for file accessing in the Developer Series. What lesson do those begin in?

Reply from Alex Hedley:

Are you wanting the File Picker?
Opening explorer and finding a file

This is covered in the Imaging Seminar

Kenny N on 2/6/2015: I want to learn the fastest and most efficient way to open, query, update, and close SQL files in VBA.

Reply from Alex Hedley:

Ah thought you meant files.

If you are wanting to know about SQL you could do the SQL Seminar Series was is awesome

Or you could start at Access 320 (Advanced Access Recordsets) onwards
This is using Access 2003 but the concepts haven't changed and the developer series holds well.

Richard Wilson on 4/12/2015: When I feel clever, I need to tell you so you can tell me if I'm wrong. I use Prefix, FName, MName, LName & Suffix concatenated into FullName. This is for data that continues to be entered from time to time. Trim works to get rid of the extra spaces if there is no Prefix or Suffix, and now Replace gets ride of the extra space if there is no MName. However, for the Trim function I concatenated all five fields and called it X, then used Replace on X to convert two spaces to one. I think it works!!! Do you see a problem?

Reply from Alex Hedley:

It's hard to cover every option but trim is a good way of getting rid of it.
You could have a load of IIF statements
Or you could use the + instead of &

Richard W on 4/12/2015: Thank you. Since this is a limited sort of problem, I think it works for me.
vicki Hudson on 9/26/2015: This course is such a good refresher/clarifying class!!
Richard Wilson on 3/24/2016: Excellent class so far, however my problem does not seem to fit. I have a many to many relationship between people and groups with a junction table. I want to find out who is in group A, who is in Group B and who is in both Group A and Group B. I thought it might come up here with the logical functions but it does not. Where do I look for an answer?

Reply from Alex Hedley:

A WHERE clause is first covered in Expert 3
You could create 3 separate queries each with a different WHERE clause
You could use a UNION query to get A and B.

 

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