Ms Access 简明教程

MS Access - Built-In Functions

在本章中，我们将使用内置函数。在 Access 中，内置函数接近 100 个，不可能涵盖所有函数。在本章中，我们将涵盖基本结构、语法以及使用一些常见的函数，还有一些需要注意的事项，以便你能自己探索其他一些函数。

In this chapter, we will be working with Built-in Functions. In Access, there are close to a hundred built-in functions and it is almost impossible to cover every single one of them. In this chapter, we will cover the basic structure, syntax, and use some of the more popular functions, and also the pitfalls, so that you can go exploring some of the other functions on your own.

Functions

函数是执行任务或计算并返回结果的 VBA 过程。函数通常可以用于查询，但还有其他可以使用函数的地方。

A function is a VBA procedure that performs a task, or calculation, and returns a result. Functions can generally be used in queries, but there are other places that you can use functions.

  1. 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 the date or the Now function to call up the current date/time information from your system, and input that value automatically.

  2. You can also use functions in an expression when you create a calculated field, or use the functions inside form or report controls. You can use functions even in macro arguments.

  3. Functions can be quite simple, requiring no other information to be called, or, simply reference one field from a table or query.

  4. On the other hand, they can also get quite complicated, with multiple arguments, field references, and even other functions nested inside another function.

现在让我们了解一些使用内置函数的示例。

Let us now look into some examples of using built-in functions.

Date & Time Functions

现在让我们了解日期和时间函数 −

Let us now understand the Date and Time functions −

  1. The Date() function is designed to return the current system date. This function does not require any function arguments or additional information. All you have to do is write the name of the function and those open and close parentheses.

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

  3. The Time() Function returns the current system time only and the Now() Function returns both the current system date and time.

  4. Depending on the data that you want to track, or store, or query, you have three built-in, easy-to-use functions to help with that task.

现在让我们打开数据库，并使用查询设计创建一个新查询，然后添加 tblProjects 和 tblTasks。

Let us now open your database and create a new query using query design and add tblProjects and tblTasks.

add tables

从 tblProjects 添加 ProjectName，从 tblTasks 添加 TaskTitle、StartDate 和 DueDate，然后运行查询。

Add ProjectName from tblProjects and TaskTitle, StartDate and DueDate from tblTasks and run your query.

reconnecting

现在你可以看到来自所有项目的不同任务。如果你想要查看截至今天日期的正在进行中的项目任务，那么我们必须使用 Date() 函数指定一个条件来查看在今天或之后开始的项目。

You can now see all the different tasks from all projects. If you want to view the project tasks that are in progress as on today’s date, then we have to specify a criterion using a Date() Function to look at projects that start on or after today’s date.

我们现在来具体说明 StartDate 下的条件。

Let us now specify the criteria underneath the StartDate.

date function

条件以一个运算符 greater than symbol 开头，其后是一个 equal to symbol 再加上一个 Date Function 。

The criteria starts with an operator greater than symbol, followed by an equal to symbol and then Date Function.

当我们运行此查询时，所有任务要么出现在今天的日期，要么出现在未来日期，如以下屏幕截图所示。

When we run this query, all the tasks will occur either on today’s date or in the future as in the following screenshot.

today date

这是一个关于如何将 Date() 函数用作查询条件的示例。

This was an example of how you can use the Date() function as query criteria.

  1. Let us now say this query needs to be more flexible in terms of the dates it is pulling starting this week.

  2. We do have a couple of different tasks that began this week, that are not showing up in this current list, because of our criteria. It’s looking at start dates that are equal to today or above.

如果我们要查看已开始但尚未完成或应在今天完成的本周的任务，让我们返回到设计视图。

If we want to view the tasks that started this week, that have not yet completed or should complete today, let us go back to the Design View.

在此，我们将会向这些条件添加一些附加信息。事实上，我们希望它大于或等于今天的日期减去七天。

Here, we will add some additional information to these criteria. In fact, we want it greater than or equal to today’s date minus seven days.

greater than date

如果我们键入减去七然后运行查询，您还将看到本周开始的任务。

If we type minus seven and run the query, you can see the tasks that started this week as well.

started task

DateDiff() Function

DateDiff() 函数是另一个非常流行的日期/时间函数。DateDiff 函数返回一个变体（长整型），指定两个特定日期之间的时间间隔数量。换句话说，它可以计算两个日期之间的差值，而且您可以选择函数用以计算该差值的间隔。

The DateDiff() Function is another very popular date/time function. The DateDiff Function returns a Variant (long), specifying the number of time intervals between two specified dates. In other words, it calculates the difference between two dates, and you get to pick the interval by which the function calculates that difference.

现在让我们假设我们要计算作者的年龄。为此，我们首先需要创建一个新查询并添加作者表，然后添加 FirstName、LastName 和 BirthDay 字段。

Let us now say we want to calculate our authors' age. For this, we first we need to create a new query and add our authors table and then add FirstName, LastName, and the BirthDay fields.

datediff

我们可以通过计算出生日期或生日与今天的日期之间的差值来计算人们的年龄。

We can calculate people’s age by calculating the difference between their date of birth, or birthday and whatever today’s date is.

让我们尝试在一个新字段中使用 DateDiff 函数。

Let us try using the DateDiff Function in a new field.

add datediff

我们称之为年龄，后加一个冒号，然后编写 DateDiff 函数。

Let us call it Age followed by a colon, and then write DateDiff Function.

  1. The first function argument for the DateDiff function is the interval, so type “yyyy”.

  2. The next function argument is the first date that we want to calculate by, which, in this case, will be the Birthday field.

  3. The third function argument is whatever today’s date is.

现在，运行您的查询，您将看到新字段，该字段显示每个作者的年龄。

Now, run your query and you will see the new field which shows the age of each author.

age of each author

Format() Function

Format() 函数返回一个字符串，其中包含一个表达式，该表达式根据一个格式表达式中包含的说明进行了格式化。以下是 Format() 函数中可以使用的用户定义格式列表。ss

The Format() Function returns a string, containing an expression formatted according to instructions contained in a format expression. Here is the list of user-defined formats which can be used in Format() function.ss

Setting

Description

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Second

现在，我们回到你的查询，并使用 Format() 函数在其中添加更多字段。

Let us now go back to your query and add more fields in the same using the Format() function.

format function

输入 Format 函数。第一个函数参数将是一个表达式，它几乎可以是任何东西。现在，我们让 birthday 字段是第一个，接下来要做的是编写我们的格式。在这种情况下，我们需要月份、月份、天、天。用引号写上“mmdd”，然后运行你的查询。

Type the Format Function. The first function argument will be an expression, which can be almost anything. Let us now have the birthday field as the first and the next thing is to write our format. In this case, we need month, month, day, day. Write “mmdd” in quotes and then, run your query.

argument expression

它现在正在从 birthday 字段获取日期，4 是月份，17 是天。

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

让我们在下一个字段中添加“mmm”和“mmmm”，而不是“mmdd”，如下面的屏幕截图所示。

Let us add “mmm” and “mmmm” instead of “mmdd” in the next fields as in the following screenshot.

add month

运行你的查询，您将看到结果，如下面的屏幕截图所示。

Run your query and you will see the results as in the following screenshot.

month name

在下一个字段中，它将返回该生日中月份的前 3 个字符，在最后一个字段中，您将获得完整的月份名称。

In the next field, it is returning the first 3 character from the name of the month for that birthday and in the last field you will get the full month name.

要查看生日之后的月份和年份，让我们添加“yyyy”，如下面的屏幕截图所示。

To see the month followed by year from birthday, let us add the “yyyy” as well as shown in the following screenshot.

year

让我们再次运行你的查询。

Let us run your query again.

month year

您现在将看到月份后面跟着一个逗号，然后是年份。

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

IIf() Function

IIf() 函数是“Immediate If”的缩写，此函数将表达式评估为真或假，并为每个表达式返回一个值。它最多有三个函数参数，所有这些参数都是必需的。

The IIf() Function is an abbreviation for “Immediate If” and this function evaluates an expression as either true or false and returns a value for each. It has up to three function arguments, all of which are required.

  1. The first argument is any expression that you want to evaluate.

  2. The next argument stands for the true part, which can be a value or an expression returned if your first expression is true.

  3. The last argument is what you want returned if your expression is false.

Example

我们举一个简单的例子。我们将使用查询设计创建一个新查询，添加 tblAuthors 表，然后添加以下字段。

Let us take a simple example. We will create a new query using query design and add tblAuthors table and then add the following fields.

immediate if

您现在可以看到我们有三个字段——FirstName、MiddleInitial、LastName，然后是这个连接字段，它将所有三个字段都拉在一起。让我们运行你的查询来查看此查询的结果。

You can now see we have three fields — FirstName, MiddleInitial, LastName, and then this concatenated field, which is pulling all three fields together. Let us run your query to see the result of this query.

concat fields

现在，您可以看到查询的结果，但您也会注意到有些记录没有中间名首字母。例如，Joyce Dyer 记录没有中间名首字母，但在 FullName 字段中，您将看到实际不需要的句点。因此，返回到设计视图。在这里，我们将使用 IIf 函数以不同的方式连接名称。

Now, you can see the result of the query, but you will also notice that some records do not have a middle initial. For example, the Joyce Dyer record does not have a middle initial, but in the FullName field you will see the period that really doesn’t need to be there. So, go back to the Design View. Here, we will concatenate the name in a different way using the IIf Function.

different way

让我们在另一个字段中写出名称并将其称为 FullName1，然后键入 IIf 函数。

Let us write the name in another field and call it FullName1 and then type the IIf function.

  1. The first function argument for the Immediate If function is going to be your expression. In the expression, we will see if the middle initial field is blank or is null.

  2. The next argument is the true part. So, if the middle initial is null then we would want to display the FirstName and the LastName.

  3. Now, for our false part — if the MiddleInitial is not null, then we would want to display the FirstName, MiddleInitial, and LastName.

现在让我们运行您的查询，您将看到结果，如下面的屏幕截图所示。

Let us now run your query and you will see the results as in the following screenshot.

display result