Introduction

Welcome! String & Logical Functions Guide

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 25. In this course we will begin a comprehensive guide to Microsoft Access functions, focusing on string functions and logical functions. We will briefly review some functions from previous lessons, discuss new features, and provide tips for both older and newer versions of Access. I will explain the course structure, prerequisites, how to use the student forums, and recommend the best way to follow along with the material. We will also talk about using sample databases and what to expect in each lesson, including upcoming topics on string and logical functions.

Navigation

Prev: X25
Next: Lesson 01

Keywords

Access Expert, string functions, text functions, logical functions, Switch function, Access functions guide, event programming, macros, Visual Basic for Applications, SQL programming language, loan amortization, web-based databases, forms and reports, acc

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 25 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is part one of our comprehensive guide to Access functions. We are going to be taking the next couple of classes to cover all the different functions you could possibly ever want to use in Microsoft Access. Today's class will focus on string functions and logical functions. Some of these functions we have covered in previous lessons. I will be going over them briefly and/or showing you some new tips and tricks regarding those functions, and some of these functions are brand new, even for me.

Prerequisites: this class follows Access Expert Level 24, and the couple of classes before it covered some advanced topics that you do not necessarily have to know to watch this class. However, before continuing, I strongly recommend you finish my beginner series and preferably expert levels one through four at least, where I begin to cover functions. If you have some of the background on functions before watching today's class, it will definitely help you.

This class is recorded with Access 2013. If you are using 2007 or 2010, you should be able to follow along with this class. Most of the functions that I am covering were around in Access 2003, however, a couple of them were new in 2007. Some of them, like Switch, will not be available to you if you are still using Access 2003 or earlier. It is time to upgrade.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you are in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels: Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion. If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you. Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now, let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are going to begin learning about string functions, also called text functions. In lesson two, we are continuing with string functions. In lesson three, we will begin looking at logical functions. In lesson four, we are continuing our look at logical functions.

Quiz

Q1. What are the two main types of functions covered in today's class?
A. String and logical functions
B. Number and date functions
C. Aggregate and domain functions
D. Mathematical and reference functions

Q2. Why is completing the beginner and at least expert levels one through four recommended before watching this class?
A. Because prior levels cover all advanced programming required
B. Because they introduce fundamental concepts about functions
C. Because they provide free database downloads
D. Because they focus only on reports and forms

Q3. Which version of Microsoft Access will NOT support all the functions demonstrated in this class?
A. Access 2013
B. Access 2010
C. Access 2007
D. Access 2003

Q4. What is the focus of expert-level Access courses according to the instructor?
A. Introducing students to VBA programming
B. Covering core topics for complete beginners
C. Teaching advanced queries and function usage
D. Building inventory management databases

Q5. Where can students ask questions about the topics covered in the lessons?
A. Only by emailing the instructor
B. In the student forums on accesslearningzone.com
C. By calling the tech support hotline
D. Only during live webinars

Q6. What does the instructor recommend students do first when taking a lesson?
A. Immediately jump into creating their own projects
B. Watch the lesson all the way through before practicing
C. Only read the accompanying text document
D. Memorize all function definitions

Q7. What is one of the advantages mentioned for downloading the finished database file?
A. It lets you skip watching the class videos
B. It can be used for any kind of business implementation directly
C. It allows students to learn by examining a working example
D. It is required to pass the course quiz

Q8. What is the first topic to be covered in today's class lessons?
A. Logical functions
B. String functions
C. Macros and event programming
D. Working with images and attachments

Q9. What kind of additional learning resources does AccessLearningZone.com offer besides regular courses?
A. Only newsletters
B. Only downloadable code snippets
C. Seminars on specific topics like web databases and calendars
D. In-person tutoring only

Q10. According to the video, what should a student do if they get stuck during the lesson?
A. Simply skip the hard parts
B. Watch the video again or ask for help in the student forum
C. Wait for live classes instead
D. Restart the entire course from the beginning

Answers: 1-A; 2-B; 3-D; 4-C; 5-B; 6-B; 7-C; 8-B; 9-C; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is part one of my in-depth guide to Microsoft Access functions. In this session, we'll begin exploring all of the different functions you might want to use in Access. Our focus today will be on string functions and logical functions. Some of these functions have been discussed in previous lessons, so I will review them and share some additional tips and tricks. There are also a few functions that are relatively new, even to me, that I'll introduce for the first time.

Before getting started, you should know that this lesson is meant to follow Access Expert Level 24. While the previous couple of classes covered some advanced topics that are not essential for today, I highly recommend completing my beginner series and at least the first four levels of the expert series. Having a solid foundation in functions will make today's lesson much clearer.

I recorded this course using Access 2013. If you're using Access 2007 or 2010, everything here should still apply. Most of the functions were available in Access 2003, but there are a few that only became available in Access 2007. For example, the Switch function is not in Access 2003 or earlier, so if you're working with an older version, it may be time to upgrade.

My teaching series is divided into several levels: beginner, expert, advanced, and developer. The beginner series is aimed at those just starting out, and you should be comfortable with everything in those classes before tackling the expert level, which is where we are now. Once you complete all the expert classes, the advanced series will introduce event programming and macros, and the developer series will take you into Visual Basic for Applications. Each level in each series builds step by step so you can progress at your own pace.

In addition to the standard series, I also offer seminars focusing on specific areas. These include building web-based databases, creating calendar-style forms and reports, improving database security, working with images and attachments, managing work orders, tracking accounts payable, learning SQL, creating loan amortization schedules, and much more. If you want more details about these seminars, visit my website at accesslearningzone.com.

Whenever you have questions about what we cover in class, you can ask them in my student forums. If you're watching this course in the online theater on my website, you'll find a dedicated forum window next to the class video for each lesson. Here, you can read other students' questions and my answers, as well as see comments from your peers. Please look through these discussions as you proceed through the lessons and join in if you like. If you're not viewing the lesson on my site, you can always visit the forums later at accesslearningzone.com/forums.

To get the most value from this course, I suggest you first watch the lesson all the way through without trying to follow along on your own computer. Then, replay it and go through the same steps that I show, building the sample database exactly as I do. Don't try to adapt these techniques to your own projects until you fully understand and master the example from the lesson. If something is unclear or you run into problems, rewatch the video or reach out on the student forum. Remember that most people find Access much easier after they get over the initial learning curve, so keep an open mind.

Although I recommend that you create the database from scratch along with me, you can also download a finished copy from my website at accesslearningzone.com/databases. Sometimes, the best way to learn is by examining a finished example. This is actually how I learned Access myself years ago by picking apart the Northwind Traders sample database included with Access. I provide a sample database for each course on my website.

Let me outline what we'll be covering today:

In lesson one, we'll get started with string functions, also called text functions. Lesson two continues with more string functions. Lesson three moves into logical functions, and lesson four builds on our exploration of logical functions.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

String functions overview
Using string functions in Access
Text manipulation functions
Concatenation of text fields
Left, Right, and Mid functions
Len function
Instr function
InstrRev function
LCase and UCase functions
Trim, LTrim, and RTrim functions
Logical functions overview
IIf function usage
Switch function in Access
Advanced logical expressions
Combining string and logical functions

Article

Welcome to Microsoft Access Expert Level 25, where we will be taking a deep dive into some of the most important and powerful tools available to you in Access: functions. In this guide, I will walk you through everything you need to know about string functions and logical functions, including how to use them and some helpful tips to get the most out of your databases.

Before we begin, it is important to have some prior experience with Microsoft Access, especially the basics. This tutorial is intended for users who have completed the beginner level training and preferably at least the first four expert levels, as those cover the foundations of working with functions. While you do not need to be an advanced user yet, having this background knowledge will make learning the topics in this article much easier.

For reference, this article is based on Access 2013. If you are using Access 2007 or 2010, you will have access to nearly all the same functions we discuss, as most of them have been part of Access since 2003 or earlier. However, there are a couple, like the Switch function, that are not available if you are using Access 2003. If you are still on an earlier version, it is a good idea to upgrade to take advantage of more features and modern security.

Access provides different classes of functions you can use to manage and analyze your data. These include string (or text) functions, which work with text fields, and logical functions, which help you make decisions or test values in your queries, forms, and reports. Understanding these functions is essential for building dynamic and powerful databases.

String functions allow you to work with text. Some common string functions in Access include Left, Right, Mid, Len, Instr, Replace, and Trim. For example, if you have a field containing a person's full name, you might use the Left function to display only the first letter or the Mid function to pull out the last name. The Trim function is handy for removing extra spaces from a field. Here are some practical examples:

Left([FirstName], 1) returns only the first letter of the FirstName field.
Mid([FullName], InStr([FullName], " ") + 1) returns the last name if the FullName field contains both the first and last name separated by a space.
Len([SomeText]) gives you the number of characters in the SomeText field.
Replace([Address], "Street", "St") replaces the word "Street" with "St" in your Address field.

You can use these functions in queries and calculated fields to create more useful and readable data. String functions are very versatile and can be combined as needed to clean up or adjust your data.

Logical functions help you apply decision-making logic in your database. The most common logical function in Access is the IIf function, which stands for Immediate If. It allows you to return one value if a condition is true and another if it is false. The basic structure of IIf is as follows:

IIf([OrderTotal] > 100, "Large", "Small")

In this example, if the OrderTotal field is greater than 100, the expression returns "Large". If not, it returns "Small". Logical functions are used everywhere in Access, from calculated fields in queries to control sources in forms and reports.

Another useful logical function is Switch, which allows you to test multiple conditions and return a value for the first condition that is true. The structure looks like this:

Switch([Score] >= 90, "A", [Score] >= 80, "B", [Score] >= 70, "C", True, "F")

Here, depending on the value of Score, a letter grade is returned. If Score is 90 or above, you get an "A"; if it is between 80 and 89, you get a "B", and so on. If none of the prior conditions is true, "F" is returned. This function is not available in Access 2003 or earlier, so make sure to use a newer version if you want to take advantage of it.

In addition to these, Access offers logical functions like IsNull, which returns True if a value is null, and Not, which can reverse a logical result. For example, IsNull([DateShipped]) will return True if the DateShipped field is empty. These logical functions help keep your data clean and allow you to build smart behaviors into your database.

When learning functions in Access, the best method is to first observe and understand how they work, then try building your own examples. I always encourage students to recreate the sample database I demonstrate, step by step, so you understand exactly how each function operates. Once you are comfortable, you can start applying what you have learned to your own projects. If you get stuck, go back to the basics, check your syntax, or consult the official documentation for the function you are using. Sometimes, the easiest way to learn is by examining a database that someone else has built, such as the sample files available at accesslearningzone.com/databases or tearing apart a template like the Northwind Traders database that comes with Access.

To get the most out of your practice, work through each example fully. Watch for common pitfalls, such as missing commas, unmatched parentheses, or using the wrong data type. Always test your expressions on sample data before deploying them across your entire database.

Mastering string and logical functions in Microsoft Access opens the door to making your databases far more efficient, accurate, and user-friendly. By combining your understanding of these functions, you can automate tasks, validate data, and create cleaner, more dynamic reports and forms. Keep practicing and experimenting, and you will find that Access becomes easier and even enjoyable to use as your skills grow.