Microsoft Access Tutorial - 1 Hour, 33 Minutes
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:
Right, Mid, InStr, InStrRev, Len
- LCase, UCase, Trim,
Replace, Str, CStr, StrComp, ASC, CHR
IsNull, IsNumeric, IsError
NZ, VarType, TypeName
And, Or, Not, Xor, IIF, Nested IIF
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
Access Expert Level 25
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.
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.
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.
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.
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.
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.
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
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.
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."
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
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
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.
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.
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
00. Intro (5:44)
01. String Functions 1 (26:27)
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
Greater, Less, Equal, NULL
ASCII Code Table
Kerning, Kerned Fonts
Repeat a String X Times
03. Logical Functions 1 (14:23)
Replace NULL Value
Determining Data Types
OR, NOT, XOR
04. Logical Functions 2(26:33)
Student Grades Pass/Fail
Student Letter Grades
Calculating Employee Overtime Pay
Separating First, Middle, Last Names
05. Review (5:44)
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
You may want to read these articles from the 599CD News: