MS Access built-in functions

In this chapter, we'll use built-in functions. I n Access, there are nearly a hundred built-in functions, and it is almost impossible to cover every one of them. lf.

Function

A function is a VBA process that performs a task or calculates and returns results. Functions can usually be used in queries, but there are other places where they can be used.

• You can use functions in table properties, for example, if you want to specify a default value for a date/time field, you can use a date or Now function to call the current date/time information from the system and automatically enter the value.

• You can also use functions in expressions when you create calculated fields, or functions in forms or report controls. Functions can be used even in macro parameters.

• Functions can be simple, do not need to call additional information, or simply refer to a field in a table or query.

• On the other hand, they can also become quite complex, with multiple parameters, field references, and even other functions nested within another function.

Let's look at some examples of using built-in functions.

Date and time functions

Now let's know Date and time functions -

• The Date() function is designed to return the current system date. T his function does not require any function parameters or additional information. All you need to do is write the name of the function and those opening and closing parentheses.

• There are two very similar built-in functions Time() and Now().

• The Time() function returns only the current system time, and the Now() function returns the current system date and time.

• Depending on the data you want to track, store, or query, you have three built-in, easy-to-use features to help perform the task.

Let's open your database now, create a new query using the query design, and add tblProjects and tblTasks.

Add ProjectName from tblProjects, TaskTitle, StartDate, andDueDate from tblTasks, and run queries.

You can now see all the different tasks for all the projects. I f you want to see a project task that is currently in progress, we must use the Date() function to specify a condition to view items that start on or after that date.

Now let's specify the conditions below StartDate.

The standard starts with an operator larger than the symbol, followed by an equal symbol, followed by a date function.

When we run this query, all tasks will occur today or in the future, as shown in the following screenshot.

This is an example of how to use the Date() function as a query condition.

• For example, this query needs to be more flexible on the date that starts this week.

• We have several different tasks that start this week and are not shown in this current list because of our standards. It appears to be equal to the start date of today or above.

If we want to see tasks that started this week that have not been completed or should be completed today, let's go back to the design view.

Here, we'll add some additional information to these standards. In fact, we want it to be greater than or equal to today's date minus 7 days.

If we enter minus 7 and run the query, you can see the tasks that start this week.

DateDiff() function

The DateDiff() function is another very popular date/time function. T he DateDiff function returns Varint (long), specifying the number of time intervals between two specified dates In other words, it calculates the difference between the two dates, and you can select the interval at which the function calculates the difference. 。

Now, for example, we want to calculate the age of the author. To do this, we first need to create a new query and add the autos table, and then add the FirstName, LastName, and WasDay fields.

We can calculate a person's age by calculating the difference between their date of birth or date of birth and today's date.

Let's try the DateDiff function in a new domain.

Let's call it Age, followed by a colon, and then write the DateDiff function.

• The first function argument of the DateDiff function is interval, so type "yyyy".
• The next function argument is the first date we want to calculate, in which case it will be the Wasday field.
• The third function argument is today's date.

Now, run the query and you'll see a new field that shows the age of each author.

Format() function

The Format() function returns a string that contains expressions formatted according to the instructions contained in the format expression. The following is a list of user function.ss formats that can be used in Format() formats

Set up	Describe
yyyy	Year
Q	Quarter
M	Month
Y	Day of year
D	Day
W	Weekday
Ww	Week
H	Hour
N	Minute
s	Second

Now let's go back to your query and add more fields to the same field using the Format() function.

Type the formatting function. T he first function argument will be an expression that can be almost anything. b21> let's take the birthday field as the first one, and the next one is to write our format. ry. rite "mmdd" in quotation marks and run the query.

It now takes the date from the birthday field, 4 is the month and 17 is the day.

Instead of "mmdd", we add "mmm" and "mmmm" to the fields below, as shown in the screenshot below.

Run the query and you'll see results like the screenshot below.

In the next field, it returns the first 3 characters from the month name of the birthday, and in the last field, you get the full month name.

To see the month from the birthday, we're going to add "yyyy," as shown in the screenshot below.

Let's run the query again.

You will now see the month followed by a comma, followed by the year.

IIf() function

The IIf() function is an abbreviation for "Immediate If" and evaluates the expression as true or false and returns a value for each. It has up to three function parameters, all of which are required.

• The first argument is any expression to evaluate.
• The next argument represents the real part, and if the first expression is true, it can be a value or an expression.
• The last argument is what you want to return if your expression is false.

Example

Let's give a simple example. We'll use the query design to create a new query, add the tblAuthors table, and then add the following fields.

You can now see that we have three fields - FirstName, MiddleInitial, LastName, and then this connection field, which pulls all three fields together. Let's run your query to see the results of this query.

Now you can see the results of the query, but you'll also notice that some records don't have intermediate initials. b20> ys. ere, we'll use the IIf function to connect names in different ways.

Let's write the name in another field, name it FullName1, and type the IIf function.

• The first function argument of the Immediate If function will be your expression. In the expression, we'll see if the initial field in the middle is blank or empty.

• The next argument is the real part. So, if the intermediate is null, then we'll show FirstName and LastName.

• Now, for our error part - if MiddleInitial is not null, then we will show FirstName, MiddleInitial and LastName.

Now let's run the query and you'll see the results as shown in the screenshot below.

---