Introduction

Welcome! Math & Type Conversion Functions

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

Welcome to Microsoft Access Expert Level 26. In this course we will continue our Comprehensive Microsoft Access Function Guide by focusing on math functions and type conversion functions. We will review some familiar functions, explore new ones such as absolute value, sine, round, int, fix, val, square root, exponentials, logarithms, trigonometric functions, modulus, integer division, and random number generation. We will also discuss converting between data types. Prerequisites and recommendations are discussed, along with details on using the student forums, following along with examples, and where to find resources and sample database files.

Navigation

Prev: X26
Next: Lesson 01

Keywords

Access Expert, math functions, type conversion, string functions, logical functions, absolute value, trigonometry, round function, int function, fix function, val function, square root, exponential, logarithm, degrees to radians, random numbers, modulus

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

Today's class is part two of our Comprehensive Microsoft Access Function Guide. Our goal is to learn just about everything about all the different functions in Microsoft Access.

Part one, the last class, Expert 25, covered the string functions and the logical functions. Today's class will focus on math functions and type conversion functions. Some of these functions we've seen before, so I'll give you a brief review of them, but a lot of them are brand new.

Prerequisites: again, I strongly recommend you have taken Access Expert Level 25, that is Part 1 of the Function Guide series. Of course, I strongly recommend you have taken the complete beginner series and all of the Expert classes before 25 as well.

This class was recorded with Microsoft Access 2013. I do believe that all of the functions covered in today's class work with most previous versions of Access, including 2010 and 2007. Of course, if you are using 2007 or 2010, I strongly recommend you upgrade to Microsoft Access 2013. It's a much better version, and a lot of bugs have been fixed. With Microsoft's new Office 365 subscription plan, the cost is no longer a problem for most people.

If you are using Access 2003 or earlier, and yes, I know some of you still do, I cannot guarantee that all of these functions exist in those older versions. I do not even have those installed on my machine anymore, so I really cannot say for sure.

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're in now. When you finish all of 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've 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.

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 math functions. We are going to learn about absolute value, sine, round, int, fix, and val.

In lesson two, we are going to learn about the square root, exponential, and logarithm functions.

In lesson three, we are going to learn the trigonometry functions in Access. We are going to learn how to calculate pi, how to convert degrees to radians and vice versa. We will learn about the sine, cosine, tangent, and arc tangent functions, and we will learn how to calculate the height of a building.

In lesson four, still working with math functions, we are going to learn about modulus, integer division, and we will do a bunch with random numbers. We will build a dice rolling form, and you will learn how to pull up random customer records.

In lesson five, we are going to learn about the type conversion functions, such as convert to integer, long, double, date, string, currency, and more. These are used for converting from one type of data to another.

Quiz

Q1. What is the primary focus of Access Expert Level 26?
A. Math functions and type conversion functions in Access
B. Creating advanced reports in Access
C. Database security and encryption in Access
D. Importing data from Excel into Access

Q2. Which prerequisite is strongly recommended before taking Access Expert Level 26?
A. Access Advanced Level 1
B. Access Beginner Level 1
C. Access Expert Level 25
D. Access Developer Level 1

Q3. Which versions of Microsoft Access are all the functions in this course expected to work with?
A. Only Access 2013 and newer
B. Access 2013, 2010, and 2007
C. Only Access 2007
D. Access 2016 and older

Q4. What is one of the main advantages of upgrading to Access 2013 according to the video?
A. It is free for everyone
B. It supports larger databases
C. It is less buggy and has more features
D. It requires less RAM to run

Q5. What will be covered in Lesson One of this course?
A. Importing data from external sources
B. Math functions such as absolute value, sine, round, int, fix, and val
C. Formatting reports with themes
D. Creating subdatasheets

Q6. Which seminar topics are mentioned as being available on accesslearningzone.com?
A. Video editing and animation
B. Building web-based databases and creating forms that look like calendars
C. Advanced Excel formulas
D. Mobile app development

Q7. Where can students find sample databases to download and study?
A. On the Microsoft Office website
B. On accesslearningzone.com/databases
C. In the Access program under Help
D. Via email request to the instructor

Q8. What is the recommended approach for getting the most out of each lesson?
A. Take written notes during the video
B. Apply the concepts to your own project immediately
C. Watch the video fully, then follow along with the exact examples
D. Only read the transcript and skip the video

Q9. If a student has a question about the lesson, what should they do?
A. Email Microsoft support
B. Visit the student forums on accesslearningzone.com
C. Search YouTube for a related video
D. Wait for the next class to ask

Q10. Which topic is NOT mentioned as being covered in this course or its seminars?
A. Building web-based databases
B. Creating forms that look like calendars
C. Tracking accounts payable
D. Creating PowerPoint presentations

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

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 Microsoft Access Expert Level 26, where I continue guiding you through Microsoft Access functions. In this class, I'm going to cover both math functions and type conversion functions. Some of these functions might look familiar if you've taken the earlier courses, so I'll briefly review those, but there are quite a few new ones that I will explain in detail.

Before you start with this course, I highly recommend that you complete Access Expert Level 25, which was the first part of this comprehensive function guide. It's also important that you have a solid understanding of all the beginner lessons and expert classes up through Level 25.

I recorded this class using Microsoft Access 2013, but just about all the functions I discuss here are available in the 2010 and 2007 versions as well. Still, I suggest upgrading to Access 2013 if possible because it is far more stable and comes with newer features and fixes. Office 365's subscription plans have made upgrading easier for most people. If you're still back on Access 2003 or an even earlier version, I cannot promise that all the functions will be the same. In fact, I don't even have those old versions installed anymore, so I can't guarantee anything about their compatibility.

My courses are broken up into beginner, expert, advanced, and developer levels. The expert classes, like this one, build on the foundation from the beginner series. Once you finish the expert levels, you will move on to advanced topics like event programming and macros, and eventually into developer-level courses that cover things like Visual Basic for Applications (VBA). Each stage of learning is divided further into level 1, 2, 3, and so on, to organize the materials into manageable pieces.

Aside from the main series, I also offer focused seminars that go in-depth on particular topics. These seminars cover things like building web-based databases, designing calendar-style forms and reports, securing your database, using images and attachments, handling work orders, tracking accounts payable, learning SQL, building loan amortization calculations, and more. You can get more details about these seminars and other resources on my website at accesslearningzone.com.

If you have questions while you're working through today's material, make sure to take advantage of the student forums. If you are watching the course in my online theater, you'll see the forum pop up right next to the class video. This is where you can post questions, see what others have asked, and read my responses along with comments from fellow students. It's a great idea to start each lesson by browsing through the forum to see if your question has already been covered.

If you're not on my website right now, you can still access the forums by visiting accesslearningzone.com/forums.

To get the best results from this course, I recommend watching each lesson all the way through first without trying to follow along on your computer. Once you've seen the lesson in full, go back to the start and work along with my examples. Build the same sample database that I create in the video, step by step. Hold off on trying to apply the new material to your own projects until you are comfortable with the examples from the lesson.

If something is unclear or you get stuck, watch the full video again or use the student forum to ask for help. Keep an open mind. Access can seem a bit overwhelming at first but, once you get used to it, you'll realize it's an incredibly powerful and user-friendly tool.

I strongly recommend building the demo database with me, but if you'd prefer to download a completed database file, you can get that from accesslearningzone.com/databases. Taking apart a finished database can be one of the best ways to learn, in fact, I taught myself Access years ago by breaking apart the Northwind Traders database that comes with the software. For each of my courses, you'll find a matching sample database available on my website.

Now let me walk you through the plan for today's class.

In our first lesson, we start exploring math functions such as absolute value, sine, round, int, fix, and val.

Lesson two is all about square roots, exponentials, and logarithmic functions.

Lesson three covers the trigonometry functions in Access, where we'll see how to work with pi, convert between degrees and radians, and use functions like sine, cosine, tangent, and arc tangent. I'll even show you how you can calculate the height of a building with these tools.

In lesson four, we will stick with math functions and move on to modulus, integer division, and working with random numbers. I'll show you how to create a form for rolling dice, and also how to pull up random customer records from your database.

Finally, lesson five is where we talk about type conversion functions, which let you convert data types - for example, converting a value to integer, long, double, date, string, currency, and more.

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

Overview of math functions in Access
Absolute value function
Sine function
Round function
Int function
Fix function
Val function
Square root function
Exponential function
Logarithm function
Trigonometry functions in Access
Calculating pi
Degrees to radians conversion
Radians to degrees conversion
Sine, cosine, and tangent functions
Arc tangent function
Calculating building height with trig
Modulus function
Integer division
Random number generation
Building a dice rolling form
Selecting random customer records
Type conversion functions overview
Convert to integer
Convert to long
Convert to double
Convert to date
Convert to string
Convert to currency

Article

Welcome to this comprehensive guide on Microsoft Access math and type conversion functions. This article aims to teach you everything you need to know about the various math and type conversion functions available in Microsoft Access. By the end, you should be comfortable using these functions in your databases to perform calculations, manipulate data, and convert between different data types.

Before diving in, you should be familiar with the basics of Access, including tables, queries, and forms. Having a solid understanding of beginner and expert concepts will help you get the most out of this tutorial. All the examples and code presented here are compatible with Microsoft Access 2013, and most features are available in Access 2007 and 2010. If you are using an older version, such as Access 2003, some of these functions may not be available.

Let us get started by looking at some of the math functions that Access provides. Math functions are powerful tools that allow you to perform calculations directly within your queries, forms, and VBA code. The following are some of the most commonly used math functions:

The Abs function returns the absolute value of a number. For example, Abs(-5) returns 5. This is useful when you need to ensure a number is always positive, regardless of its original sign.

The Sgn function returns an integer indicating the sign of a number. Sgn(-10) returns -1, Sgn(0) returns 0, and Sgn(5) returns 1. This can help you determine whether values are negative, zero, or positive.

The Int and Fix functions both return the integer portion of a number by removing the fractional part. The main difference between them is in how they handle negative numbers. Int(-4.8) returns -5 (rounds down), while Fix(-4.8) returns -4 (truncates toward zero).

The Round function lets you round a number to a specified number of decimal places. Round(3.14159, 2) returns 3.14. Use this function in financial calculations where you need a specific number of decimal places.

If you want to convert text to a numeric value, the Val function comes in handy. Val("123.45") returns the number 123.45. This is useful for converting text input to numbers, especially when dealing with user input from forms or imported data.

Next, let us explore some more advanced mathematical functions. The Sqr function returns the square root of a number. For example, Sqr(16) gives you 4. The Exp function gives you Euler's number e raised to the power you specify. Exp(1) returns approximately 2.71828. The Log function returns the natural logarithm (base e) of a number. Log(10) gives you about 2.30259.

Access also includes trigonometric functions, which can be useful in more complex calculations. The Sin, Cos, and Tan functions calculate the sine, cosine, and tangent of an angle but expect the angle in radians. For instance, Sin(1) returns the sine of 1 radian. If you need to work with degrees, you can convert degrees to radians by multiplying by Pi/180. The Atn function returns the arctangent of a number, which is the angle whose tangent is the specified number.

To calculate Pi in Access, use the Atn function like this: Pi = 4 * Atn(1). That statement will give you the value of Pi (approximately 3.14159).

As a practical example, suppose you want to calculate the height of a building based on its distance from an observer and the angle of elevation. You can use the Tan function for this. If Distance = 100 and AngleInDegrees = 30, first convert the angle to radians: AngleInRadians = 30 * (Pi / 180). Then, Height = Tan(AngleInRadians) * Distance.

If you need to find the remainder when dividing two numbers, use the Mod operator. For example, 17 Mod 5 returns 2. This is useful for tasks that repeat in cycles, such as pagination or color alternation in rows.

For integer division, Access uses the backslash operator \. For instance, 17 \ 5 returns 3 because 5 fits into 17 three times.

Suppose you want to work with random numbers. Access provides the Rnd function, which returns a random number between 0 and 1. To get random integers within a range, you can use: Int((UpperBound - LowerBound + 1) * Rnd + LowerBound). For example, to simulate rolling a six-sided die, use Int(6 * Rnd) + 1.

Pulling a random record from a table can be done by generating a random value for each row and sorting by that value. In a query, add a calculated field like RandomNumber: Rnd([ID]) and sort by RandomNumber.

Beyond math functions, Access also offers type conversion functions to help you work with different kinds of data. The CInt function converts a value to an integer. For example, CInt("123") returns 123. The CLng function is for long integers. CDbl converts a value to a double-precision floating-point number, which is useful when you need to work with very precise values. CCur converts a value to a currency type for accurate financial calculations. CStr converts data to string, and CDate converts text or numeric input to a date.

For example, if you have a form input where a user enters a date as text, you can convert it to an actual date value using CDate. If TextDate = "1/1/2020", then CDate(TextDate) returns the actual date value you can use in calculations and queries.

All these functions work in Access queries, calculated controls in forms and reports, as well as in VBA code. Here are some brief VBA examples for reference:

' Return the absolute value
Dim MyAbs As Double
MyAbs = Abs(-12.34) ' Returns 12.34

' Round to two decimal places
Dim Rounded As Double
Rounded = Round(3.14159, 2) ' Returns 3.14

' Type conversion
Dim MyInt As Integer
MyInt = CInt("42") ' Returns 42

' Generate a random number between 1 and 6
Randomize ' Optional, seeds the random number generator
Dim DieRoll As Integer
DieRoll = Int(6 * Rnd) + 1

The best way to master these functions is to practice. Create a new Access database and experiment with these functions directly in queries or VBA modules. Try combining them in different ways to suit your project needs. Remember, math and type conversion functions are essential tools for calculations, data manipulation, and transforming user input.

If you get stuck or run into unexpected results, check your data types and inputs. Practice will build your confidence and reveal how these functions can solve real-world problems in your Access applications. Keep experimenting, and soon using math and type conversion functions in Microsoft Access will become second nature.