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
Microsoft Access Expert 25
Richard on 9/21/2014:
Microsoft 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: