Semenalidery.com

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ms access iif

Функция IIF в языке T-SQL – описание и примеры

Привет! В этой заметке я расскажу о функции IIF языка T-SQL, которая предназначена для упрощения написания условных конструкций с использованием всем известного выражения CASE или операторов IF…ELSE…THEN.

Функция IIF языка T-SQL

IIF – логическая функция языка T-SQL, которая возвращает одно из двух значений в зависимости от результата логического выражения. Появилась данная функция в 2012 версии SQL Server.

Синтаксис IIF

IIF ( boolean_expression, true_value, false_value )

  • boolean_expression – логическое выражение. Если указано не логическое выражение, то возникнет ошибка синтаксиса;
  • true_value – значение, которое будет возвращено, если boolean_expression имеет значение TRUE;
  • false_value – значение, которое будет возвращено, если boolean_expression имеет значение FALSE.

Таким образом, функция IIF имеет 3 параметра, в первом параметре указывается логическое выражение, во втором мы пишем значение, которое будет возвращено, если логическое выражение вернет TRUE, в третьем — значение, которое будет возвращено, если логическое выражение вернет FALSE.

Особенности работы функции IIF

true_value и false_value могут иметь разные типы данных, при этом функция IIF вернет результат с тем типом данных, который будет иметь наивысший приоритет.

Если рассматривать внутреннюю работу функции IIF, то все действия в ней приводятся к выражению CASE и, таким образом, функция IIF является абсолютным эквивалентом выражения CASE, а создана и применяется она для более быстрого и простого написания логических выражений с использованием CASE.

Параметры true_value и false_value не могут одновременно содержать значения NULL, как минимум одно из этих значений должно отличаться от NULL, в противном случае, т.е. если указать NULL для обоих этих параметров, мы получим примерно следующую ошибку

Сообщение 8133, уровень 16, состояние 1, строка 1

Как минимум одно из результирующих выражений в спецификации CASE должно отличаться от константы NULL.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую пройти наши онлайн-курсы по T-SQL, на которых используется последовательная методика обучения специально для начинающих.

Примеры использования функции IIF в T-SQL

Сейчас давайте рассмотрим несколько примеров работы функции IIF.

Пример 1 – Простое использование функции IIF

В этом примере в качестве логического выражения мы укажем 1

Однако, стоит нам только изменить логическое выражение, к примеру мы укажем 1 > 2, то в качестве результата мы получим значение третьего параметра, т.е. FALSE, так как 1 никак не может быть больше 2.

Пример 2 – Использование функции IIF с переменными

Во всех параметрах функции IIF можно использовать переменные, в следующем примере в функцию IIF подставляются целочисленные переменные.

А в данном запросе мы подставляем уже текстовые значения.

Пример 3 – Использование функции IIF с более сложным логическим выражением

В качестве первого параметра, т.е. логического выражения, можно указывать более сложные выражения, которые будут содержать различные вычисления. Например, в следующем примере мы проверяем не просто значения двух переменных, а результат их перемножения, в данном случае мы спрашиваем, будет ли он больше 10.

Пример 4 – Вложенный IIF

Функцию IIF можно использовать в выражении внутри другой функции IIF. Например, в следующем SQL запросе мы хотим узнать, к какой категории относится товар по его наименованию. Для этого мы сначала сравниваем значение наименования товара с одним текстовым значением, а затем, в зависимости от результата, продолжаем сравнивать, но только уже с другим текстовым значением.

В данном случае первый IIF у нас вернул FALSE, поэтому мы перешли к третьему параметру, в котором у нас указан еще один IIF, а он в свою очередь вернул TRUE, поэтому в результате мы получили значение «Комплектующие ПК».

Однако, если указать в переменной значение, которое не будет соответствовать нужной нам категории, мы получим значение «Без категории».

Пример 5 – Использование функции IIF в запросе к таблице

Функцию IIF так же, как и другие функции, можно использовать в обычном SQL запросе к таблице или любому другому табличному выражению. Иными словами, в качестве параметров в функцию IIF можно передавать названия столбцов.

В этом примере мы на основе категории товара увеличиваем его цену.

Функция IIf

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Возвращает одну из двух частей в зависимости от оценки выражение.

Вы можете использовать функцию IIf везде, где можно использовать выражения. IIf используется для определения, является ли другое выражение истиной или ложью. Если выражение истинно, IIf возвращает одно значение; если оно ложно, IIf возвращает другое значение. Значения, возвращаемые IIf, устанавливаете вы.

Функция IIf имеет аргументы, указанные ниже.

Обязательный аргумент. Выражение, которое требуется оценить.

Обязательный аргумент. Значение или выражение, возвращаемое, если значение аргумента выражение является истинным.

Обязательный аргумент. Значение или выражение, возвращаемое, если значение аргумента выражение является ложным.

IIf всегда оценивает обе части если_истина и если_ложь, хотя и возвращает только одну из частей. Обратите на это внимание, чтобы избежать нежелательных последствий. Например, если при оценивании части если_ложь приходится делить на ноль, возникает ошибка, даже если выражение является истинным.

Примеры

Использование IIf в форме или отчете Предположим, что у вас есть таблица клиентов, который содержит поле с именем Страна или регион. В форме нужно указывают, является ли итальянский язык первый контакт. Можно добавить элемент управления и использование функции IIf в свойстве Control Source следующим образом:

=IIf([Страна или регион]=»Италия», «Итальянский», «Другой язык»)

Когда вы открываете форму в режиме формы, элемент управления отображает «Итальянский», если в поле «Страна или регион» указана Италия, и «Другой язык», если в поле «Страна или регион» указано другое значение.

Использование IIf в сложных выражениях Любое выражение можно использовать как любой части инструкцию IIf . Можно также «вложить» IIf выражения, позволяя вычисления ряда зависимых выражения. Чтобы продолжить работу в предыдущем примере, может потребоваться для нескольких различных значений Страна или регион, а затем отобразите нужный язык в зависимости от того, что существует значение:

=IIf([Страна или регион]=»Италия», «Итальянский», IIf([Страна или регион]=»Франция», «Французский», IIf([Страна или регион]=»Германия», «Немецкий», «Другой язык»)))

Текст «Другой язык» является аргументом если_ложь внутренней функции IIf. Поскольку каждая вложенная функция IIf является аргументом если_ложь функции IIf, которая ее содержит, текст «Другой язык» возвращается только в случае, если все аргументы выражение всех функций IIf оцениваются как ложные.

Приведем другой пример. Предположим, что вы работаете в библиотеке. В базе данных библиотеки есть таблица под названием «Выданные книги», которая содержит поле «Срок» с датой, когда нужно вернуть определенную книгу. Вы можете создать форму, которая указывает состояние выданной книги в элементе управления, с помощью функции IIf в свойстве Данные, например:

Лекция 6 Тема: Запросы. Вычисления и групповые операциию

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

6.4. Итоговые запросы.

6.5. Перекрестные запросы.

Очень часто при формировании набора записей требуется произвести вычисления над данными (определить возраст сотрудника, стоимость заказа, процент от продаж, выделить часть номенклатурного кода и т. п., т. е. вывести информацию, которая не хранится в базе данных) или выполнить определенные операции по непосредственной обработке отбираемых данных.

Читать еще:  Как удалить лист в опен офисе

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

Вычисляемое поле представляет собой выражение, состоящее из операторов (арифметических, сравнения, логических, конкатенации) и операндов. В качестве операндов могут быть использованы константы, встроенные или определенные пользователем функции и идентификаторы, например

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например

Стоимость: Цена*[Количество товара]

Вычисляемое поле создается прямо в бланке QBE путем внесения выражения в ячейку Поле любого свободного столбца. Результаты вычислений, выводящиеся в поле, не запоминаются в базовой таблице. Вычисления снова производятся всякий раз, когда выполняется запрос, поэтому результаты всегда представляют текущее содержимое базы данных.

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить….

Рис.6.1. Диалоговое окно Построитель выражений

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Рис.6.3. Диалоговое окно Построителя выражений
со списком встроенных функций

Все встроенные функции в Построителе выражений сгруппированы по функциональному назначению.

При выборе функции в правом окне построителя, в нижней области окна Построителя выражений отображается синтаксис этой функции. Для более детальной справки необходимо щелкнуть по клавише Справка.

Обзор всех встроенных функций выходит за рамки лекции, поэтому рассмотрим наиболее используемые.

Таблица 6.1

Функции категории Дата/время

Возвращает значение дня месяца от 1 до 31

Возвращает значение месяца от 1 до 12

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Возвращает значение года от 100 до 9999

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Возвращает целое число от 0 од 23, представляющее значение часа

Возвращает числовое значение в зависимости от значения аргумента интервал:

«q» – квартал (от 1 до 4);

«m» – месяц (от 1 до 12);

«yyyy» – год (от 100 до 9999);

«ww» – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Возвращает текущую системную дату

Функции категории Проверка

Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

Функции категории Управление

IIf(условие; выр1; выр2)

Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

Продолжение таблицы 6.1

Функции категории Текстовые

Возвращает n левых символов аргумента текст

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Возвращает количество символов (длину строки) в аргументе текст

Возвращает строковое значение аргумента текст без начальных пробелов

Возвращает строковое значение аргумента текст без заключительных пробелов

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Возвращает строковое значение аргумента число

Возвращает значение аргумента переменная в формате заданным аргументом формат

6.3. Итоговые запросы

При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:

— количество сделок с Партнерами за определенный промежуток времени;

— средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

Для вычисления итоговых значений необходимо нажать кнопку Групповые операции на панели инструментов Конструктор запросов, чтобы в бланке QBE появилась строка Групповая операция (после имени таблицы).

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Функции категории Статистические

Возвращает сумму набора значений

Возвращает среднее арифметическое набора значений

Возвращает наименьшее значение из набора значений

Возвращает наибольшее значение из набора значений

Возвращает количество записей в наборе значений отличных от Null

Возвращает первое значение поля в группе

Возвращает последнее значение поля в группе

Возвращает среднеквадратичное отклонение набора значений

Возвращает дисперсию набора значений

В раскрывающемся списке строки Групповая операция имеется установка Выражение. Данная установка применяется, когда в выражении (строка Поле) используется несколько итоговых функций.

В раскрывающемся списке строки Групповая операция имеется установка Условие. Данная установка применяется, когда в строке Условие отбора записано условие выборки, но данные столбца (поля) не должны участвовать в групповой операции.

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

Перекрестный запрос – это специальный тип группового запроса, в котором отражаются результаты статистических расчетов по значению одного поля таблицы.

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

В области заголовков строк допускается размещение нескольких полей, в то время как в других областях может размещаться только по одному полю.

В перекрестном запросе допускается указание условий отбора. Сортировка может проводиться только по полям, размещенным в области заголовков строк.

MS Access — Built-In Functions

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

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.

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.

Читать еще:  Значок майкрософт офис

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.

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

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 −

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.

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

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

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.

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

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

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.

Let us now specify the criteria underneath the StartDate.

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.

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

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

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.

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

DateDiff() Function

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.

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.

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

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

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

  • The first function argument for the DateDiff function is the interval, so type “yyyy”.
  • The next function argument is the first date that we want to calculate by, which, in this case, will be the Birthday field.
  • 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.

Format() Function

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

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

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.

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

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

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

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.

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

Let us run your query again.

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

IIf() Function

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.

  • The first argument is any expression that you want to evaluate.
  • The next argument stands for the true part, which can be a value or an expression returned if your first expression is true.
  • The last argument is what you want returned if your expression is false.

Example

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.

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.

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.

Читать еще:  Либре офис как поставить фильтр

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

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.

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.

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.

Using an IIf Function

Functions are used to create more complicated calculations or expressions than operators can. For example, the adds several values together, and the IPmt function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

The syntax for the IIf function

Here the IIf function evaluates the value in the First Class field and returns 50 if the First Class field is True and 25 if the First Class field is False.

There are several hundred in Access, but all of them are used in a similar way: the name of the function, followed by the arguments in parenthesis. An argument in Access is the value a function uses to perform its calculation not the heated disagreement you have over political views. For example, the argument in the formula p r2 would be r, or the radius, used to find the area of a circle.

This lesson introduces a very useful database function: the IIf function. The IIf function is a or because it evaluates a condition and returns one value if the condition is true and another value if the condition is false. For example, you could use the IIf function in an invoice to create a formula that would subtract a 5-percent discount from the invoice if the total were more than 500 dollar otherwise, the IIf Function wouldn’t subtract anything.

The IIf function contains three arguments, as shown in figure. Since you can use the to help you create IIf function formulas, you really don’t need to memorize the syntax of the function.

In this lesson you will use the IIf function to create a field that gives passengers a 50-dollar rebate if they fly first class and a 25-dollar rebate if they fly coach, as shown in figure.

Click the qryRebate query and then click the Design button.

You need to create a calculated field that will determine how much of a rebate passengers should receive. Create this field in the first blank Field row.

Click the blank Field row in the fifth column and click the Build button on the toolbar.

The Expression Builder appears.

In the bottom-left of the window, the Expression Builder displays a list of several folders that contain information. For example, the Tables folder contains a list of all the tables in the current database. These folders are displayed in a hierarchical view. A plus symbol ( ) or a minus symbol ( ) next to a folder means a folder contains several subfolders. Normally, these subfolders are hidden. You can display the hidden folders within a folder by double-clicking the folder. To see the contents of a folder, simply select the folderits contents will appear in the middle and left windows.

To use one of the built-in functions, double-click the Functions folder and select the in the bottom-left window.

Double-click the Functions folder in the bottom-left window.

The Functions folder expands and displays its contents. The Built-In Functions folder contains several hundred functions that are included in Access.

Click the Built-In Functions folder.

When you select the Built-In Functions folder in the left window, the middle window displays the function categories you can use, and the right window displays the functions in the selected category. If you know the category of the function you want to use, you can select it in the middle window to narrow down your choices in the right window.

Scroll down the middle window and find and click the Program Flow function category.

The right window displays the Program Flow functions.

Double-click the IIf function in the right window.

Access adds IIf (expr, truepart, falsepart) to the expression box. Now that you know the proper syntax of the IIf function, you need to replace the argument names with the data values. You can double-click to select any argument name so that you can replace it with your own value.

Double-click the expr argument.

You need to specify the logical testif the passenger flew first class or not.

Double-click the Tables folder in the left window, click the tblCustomers folder, and then double-click the First Class field in the middle window.

The Expression Builder adds the First Class field to the expression. Finish the rest of the logical test.

Your expression should read IIf ([tblCustomers]![First Class]=True, truepart, falsepart). Now you have to replace the truepart and falsepart arguments with the values you want to use if the IIf statement is true or false.

Double-click the truepart argument and type 50 , then double-click the falsepart argument and type 25 .

The Expression Builder closes. We need to give the new calculated control a more meaningful name.

In the new calculated field, replace the Expr1: label with Rebate:

Let’s see the results of the new query.

Click the Run button on the toolbar.

The IIf expression in the new Rebate field evaluates the First Class field and returns a 50-dollar rebate if the customer flew first class, and a 25-dollar rebate if not.

Save your changes.

Save your changes and close the query.

To create an iif (if. then) function:

display the query in design view.

click the field row of a blank column in the design grid.

enter the field name followed by a colon (:).

type the expression using the syntax iif (expr, truepart, falsepart)

click the build button on the toolbar. double-click the functions folder in the bottom-left window, click the built-in functions folder, scroll down the middle window and click the program flow category, then scroll down the right column and double-click the iif function. replace the parts of the pasted iif function with the fields and values you want, then click ok.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×