Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< 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: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
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: $28.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
Separate Phone Number Parts
Area Code, Prefix, Suffix
Separate First and Last Names
Get Last Name even if Middle Name
Convert to Upper Case
Convert to Lower Case
Convert to Proper Case
Remove Extra Spaces from String
Replace One String with Another
String Concatenation &

02. String Functions 2 (14:51)
Convert Number to String
String Comparison
Greater, Less, Equal, NULL
ASCII Code Table
Non-Standard Characters
Monospace Fonts
Kerning, Kerned Fonts
Repeat a String X Times
03. Logical Functions 1 (14:23)
Replace NULL Value
Determining Data Types
Data Type Names
Logical Operators

04. Logical Functions 2(26:33)
Nested IIF()
Student Grades Pass/Fail
Student Letter Grades
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


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/23/2024 8:44:11 AM. PLT: 0s