Access Expert Level 25

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 (eXclusive Or).

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 cleaner solution.

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.

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 25