Функция nz в access

Программирование на VBA Access 2003 – Встроенные функции

Пришло время поговорить и о программировании на VBA Access, так как мы с Вами рассматривали уже достаточно много примеров реализации тех или иных задач в Access 2003, но мы не затрагивали основы VBA, и сегодня мы начнем со встроенных функций. Рассмотрим часто используемые функции и конечно разберем примеры их использования, для лучшего освоения языка программирования VBA для Access.

Если говорить о том, что именно мы уже рассматривали в Access 2003 то, например это Загрузка данных из текстового файла на VBA Access или Выгрузка данных из Access в шаблон Word и Excel. А сегодня мы будем рассматривать именно основы VBA и как уже сказалось выше встроенные функции.

Встроенные функции VBA Access

Если Вы уже немного знакомы с какими-нибудь языками программирования, например, Вы, знаете основы JavaScript или основы Visual Basic, то Вы должны понимать что такое встроенные функции, а если нет то я могу привести свое определение данного термина.

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

И так хватит теории, давайте приступим к рассмотрению встроенных функций VBA Access.

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

Функция msgbox – простая функция для отображения сообщения на экране в виде диалогового окна.

И теперь сохраните, откройте форму и нажмите на нашу кнопку и у Вас отобразится это сообщение:

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

Примечание! Далее я буду писать все примеры без указания начала процедуры и ее конца т.е. без Private Sub Кнопка1_Click() и End Sub, подразумевается что все примеры мы будем писать в обработчике одной кнопки, Вы просто вставляйте код ниже между этим строками.

Примечание! Более подробно об описании и значениях констант можете прочитать во встроенной автономной справке Access 2003. Тоже относится и к остальным встроенным функциям и любым другим возникающим вопросам, так как в этой справке есть все, всем советую ей пользоваться.

Функция Mid – данная функция относится к строковым функциям Access и она вырезает указанное число знаков строки. Имеет три параметра, первый это собственно сама строка, второй это начальная позиция, и третий это число символов.

Здесь мы просто указали строку, из которой необходимо вырезать, и с какого символа нужно начинать, третий параметр мы не указывали (поэтому вырезалось все, начиная с 7 символа), а если захотели, то код был бы таким (абсолютно равнозначный):

Функция Len – также относится к строковым функциям и она просто считает количество символов в строке. Давайте объединим предыдущий пример с функцией len, так как порой неизвестно (да и не удобно) сколько символов необходимо вырезать.

И результат будет таким же, как и предыдущем примере.

Функция LCase – строковая функция vba которая преобразует все буквы строки в строчные. Для закрепления давайте рассмотрим все на том же примере, только сейчас обрамим наше выражение еще и функцией LCase.

И результат будет таким же, как и раньше, но все заглавные буквы станут строчными, в нашем случае это первая буква «Н», результат:

Функции left и right – это строковые функции vba, которые вырезают указанное количеств символов слева и справа соответственно. Принцип такой же, как и, например, в Excel функциях левсимв и правсимв.

Функция right работает аналогично, только вырезает символы справа.

Функции date, time, now – это функции, с помощью которых можно получить текущую дату и время. По названию я думаю понятно, какая функция для чего нужна, если нет то date текущая дата, time текущее время, now текущая дата и время. Данные функции запускаются без параметров.

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

Функции hour, minute, second – эти функции возвращают соответственно часы, минуты и секунды. Принимают один параметр это текущее время.

Функция isnull – проверяет значение на NULL.

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

Функция Nz — данная функция возвращает значение не NULL. Принимает один обязательный параметр с типом variant и один не обязательный. Она работает следующим образом, если входящий параметр NULL она преобразует его в другое значение, для предотвращения использования NULL значений в выражении. Также если указан второй параметр, то в случае если первый параметр NULL то она вернет его, т.е. второй параметр.

Пример предотвращения NULL значения в выражении:

Т.е. если бы мы не использовали данную функцию, то тогда у нас была ошибка, так как сложить число со значением NULL нельзя, но мы это предвидели и предотвратили.

Пример возвращения значения, если входящий параметр равен NULL:

Здесь просто мы смотрим если первый параметр NULL, то возвращаем второй в виде строки.

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

Функции Int, Fix, Abs – функции которые работают с числами, все они принимают один параметр и делают следующее:

Int – принимает значение типа Double и возвращает число меньшее или равное исходному числу, в случае если число отрицательное;
Fix — принимает значение типа Double и возвращает число большее или равное исходному числу, в случае если число отрицательное;
Abs – возвращает абсолютное значение числа переданное во входящем параметре.

Читать еще: Process all access

Примечание! Для перевода на новую строку мы использовали константу vbNewLine.

Пока это все, надеюсь, данные простые примеры помогут Вам освоить и понять что такое встроенные функции, а мы с Вами продолжим изучать VBA Access в следующих статьях. Удачи!

Функция Nz

Вы можете использовать функцию NZ , чтобы возвращать ноль, пустая строка («») или другое указанное значение, если значение типа Variant равно null. Например, с помощью этой функции можно преобразовать значение null в другое значение и предотвратить его распространение через выражение.

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

Обязательный аргумент. Переменная, имеющая тип данных Variant.

Необязательный аргумент (если он используется не в запросе) типа Variant. Variant , который предоставляет возвращаемое значение, если аргумент Variant имеет значение NULL. Этот аргумент позволяет вернуть значение, отличное от нуля или пустой строки.

Примечание: Если функция Nz используется в выражении запроса без аргумента Значение_если_null , в результате выполнения этой функции для полей, содержащих пустые значения, будет возвращена пустая строка.

Если аргумент Variant имеет значение null, функция NZ возвращает нулевое число или строку нулевой длины (всегда возвращает пустую строку при использовании в выражении запроса) в зависимости от того, указывает ли контекст значение. должно быть числом или строкой. Если указан необязательный аргумент валуеифнулл , функция NZ возвращает значение, заданное этим аргументом, если аргумент Variant имеет значение NULL. При использовании в выражении запроса функция NZ должна всегда содержать аргумент валуеифнулл ,

Если значение Variant не равно NULL, функция NZ возвращает значение Variant.

Замечания

Функция NZ полезна для выражений, которые могут содержать значения null . Чтобы принудительно задать для выражения значение, отличное отnull , даже если оно имеет значение null , используйте функцию NZ , чтобы возвращать ноль, строку нулевой длины или настраиваемое возвращаемое значение.

Например, выражение 2 + varX всегда будет возвращать пустое значение, если значение Variant varX равно null. Однако 2 + Nz(varX) возвращает 2.

Часто вы можете использовать функцию NZ в качестве альтернативы функции IIf . Например, в приведенном ниже коде для возврата нужного результата необходимо выполнить два выражения, включая функцию IIf . Первое выражение, включающее функцию IIf , используется для проверки значения переменной и для ее преобразования в нуль, если оно имеет значение NULL.

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

varResult = IIf(Nz(varFreight) > 50, «High», «Low»)

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

В следующем примере необязательный аргумент, предоставленный функции NZ , предоставляет строку, которая возвращается, если varFreight имеет значение NULL.

varResult = Nz(varFreight, «No Freight Charge»)

Типичные тормоза запросов

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

Мы предполагаем, что ваши таблицы содержат первичные ключи, внешние ключи и индексы на полях, по которым совершается поиск и сортировка.

Используйте SQL, а не VBA

JET/ACE (движок запросов в Аксессе) использует Structured Query Language (SQL), как и многие базы данных. JET также способен вызывать код Visual Basic for Applications (VBA). Это радикально расширяет возможности JET-а, но вызов VBA теряет смысл, если работу может выполнить SQL.

Is Null, не IsNull()

Is Null является родным выражением SQL.

IsNull() — вызов VBA функции.

Не бывает веских причин вызывать IsNull() в запросе, так как SQL может самостоятельно оценить смысл выражения.

IIf(), не Nz()

Функция Nz() заменяет Null другим значением (для чисел обычно нулем, для текста — пустой строкой.) Новое значение является типом данных Variant, а VBA помечает его в свою очередь подтипом: String, Long, Double, Date и т.д.

В VBA это просто замечательно: функция может возвращать разные подтипы в разных ситуациях. Но в запросе столбец может быть только ОДНОГО типа данных. JET, следовательно, воспринимает значения типа Variant как текстовые, так как что угодно (числа, даты, символы, . ) являются валидными в текстовом столбце.

Визуальным признаком того, что JET воспринимает столбец как текст, является выравнивание по левому краю. Числа и даты отображаются выровненными по правому краю.

Если вы ожидали числовую или колонку с датами, у вас серьезные проблемы. Текстовые поля оцениваются посимвольно. Т.е. 2 больше 19, потому что первый символ (2) больше, чем первый символ в другом тексте (1 in 19.) Подобным образом, 4/1/2009 идет после 1/1/2010 в текстовом столбца, так как 4 идет после 1.

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

Можно было бы типизировать выражение еще вызовом еще одной функции VBA, но лучшим решением было бы позволить JET выполнить работы, не вызывая VBA вообще.

Вместо:
Nz(MyField,0)
используйте:
IIf(MyField Is Null, 0, MyField)

Да, придется чуть больше напечатать, но есть свои плюсы:

Вы избегаете вызова функции Nz().
Вы сохраняете желаемый тип данных.
Критерии применяются корректно.
Столбец сортируется корректно.

Этот принцип относится не только к Nz(), но и к любой функции VBA, возвращающей Variant. Просто Nz() — наиболее распространенный случай.

(Обратите внимание: функция JET IIf() намного более эффективна, чем одноименная функция в VBA. Функция VBA тратит время на оценку и истинной и ложной части и генерирует ошибки, если какая-нибудь из частей не срабатывает (даже если эта часть не нужна.) У JET-овской функции IIf() подобных проблем нет.)

Доменные агрегатные функции

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

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

Читать еще: Visual c access

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

Составляйте выражения, чтобы использовать индексы

Запрос будет намного быстрее, если база данных может использовать индекс, чтобы выбирать или сортировать записи. Вот два примера.

Оптимизируйте групповые запросы

Оптимизатор JET-а весьма неплох, так что вам может показаться, что простые запросы будут выполняться быстро и без советов из этого раздела. Все равно стоит потраченных усилий сделать запрос как можно лучше, чтобы он не стал внезапно тормозить, когда вы его измените.

WHERE против HAVING

Итоговые запросы (те, что с предложением GROUP BY) могут иметь как предложение WHERE, так и предложение HAVING. Сначала выполняется WHERE — перед группировкой; затем следует HAVING — когда высчитываются итоги. Итак, имеет смысл поместить критерии в предложение WHERE и использовать HAVING только, когда нужно примерить критерии на итоги по группам.

В конструкторе запросов Аксесса это не очевидно. Когда вы добавляете поле в строке конструктора, Аксесс устанавливает строку Групповая операция на Группировка (Group By), и хочется добавить критерии прямо под ним. Если вы это сделаете, критерии отбора окажутся в предложении HAVING. Чтобы использовать предложение WHERE clause, добавьте поле в грид конструктора еще раз и выберите Where в строке Групповая операция.

FIRST против GROUP BY

Когда вы добавляете поле в групповой запрос, Аксесс предлагает Группировку/Group By в строке Групповая операция. Следовательно, по умолчанию Аксесс будет группировать по всем этим полям.

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

Это сильно меняет дело в ситуации с полями типа Memo. Если вы делаете GROUP BY по МЕМО-полю (Notes в примере), Аксесс сравнивает только первые 255 символов, а остальные просто отсекаются! Выбирая First вместо Группировка/Group By, JET может вернуть поле Memo полностью из первого же совпадения. Так что это не только более эффективно; это реально решает проблему усечения полей Memo.

(Минусом использования First является получение полем псевдонима, напр. FirstOfNotes.)

Прочие приемы оптимизации

Прочие предложения общего характера для оптимизации запросов в JET:

С многотабличными запросами по возможности используйте JOIN-ы. JET выполнит такой запрос быстрее, чем с предложением WHERE по внешнему ключу.
Возвращайте по возможности меньше полей. Это оптимизирует использование памяти и может сократить количество обращений к диску. Но включайте ключевые поля, чтобы дать JET-у быстрый способ идентифицировать записи.
Стройте строки запроса динамически,как показано здесь форма поиска. Особенно там, где пользователь будет вводить только несколько из возможных критериев, которые вы ему предоставляете. Это радикально упрощает критерии. Аксесс применяет фильтру интеллектуально. т.е. Filter или WhereCondition обычно применяются перед тем, как он запрашивает записи из файла с данными.
Избегайте множества таблиц на внешней стороне JOIN-а, так как JET может их неправильно интерпретировать.
Чтобы контролировать порядок выполнения, сохраните один запрос и используйте его как таблицу для другого запроса (вложенные запросы). Это важно, так как JET не считается со скобками в предложении FROM, когда он составляет план выполнения.
Подзапросы в общем менее эффективны, чем другие приемы (такие как JOIN-ы или вложенные сохраненные запросы), но более эффективны, чем доменные агрегатные функции.
Советы по перекрестным запросам см. Техника построений перекрестных запросов.
Используйте ShowPlan для JET для более подробной информации по поводу того, как JET планирует выполнение запроса.

Оптимизация запросов — огромная тема. Для дальнейшего чтения см. SQL Queries for Mere Mortals авторов Michael Hernandez и John Viescas.

Функция MS Access Nz() не распознана в MS Excel

Я пытаюсь написать выражение в MS Access, которое возвращает строку текста, «New Policy» , в операторе Iif() после того, как он возвращает ошибку.
Когда я экспортирую данные из Access в Excel, он говорит мне, что Nz() не распознается.
Есть ли альтернатива этой функции? Мое выражение доступа выглядит следующим образом:

3 Ответа

Nz определяется в библиотеке типов Microsoft Access, на которую автоматически ссылаются, когда ваше хост-приложение имеет доступ.

Если вы добавите ссылку на библиотеку типов доступа в свой проект Excel, вы сможете использовать Nz . но тогда ваш проект Excel не будет работать, если Access не установлен, а это так. странный и довольно грубый.

Что делает Nz ? Похоже на какую-то функцию Coalesce , которая должна быть довольно тривиальной для реализации самостоятельно и настройки по мере необходимости. Что-то вроде этого?

Вам не нужно Nz для этого:

Если вы используете VBA, вы можете добавить ссылку на библиотеку MS Access, например » Microsoft Access 14.0 object library. Тогда функция Nz доступна как Access.Nz(value, «New Policy») . Вы даже можете создать пользовательскую функцию для использования в электронных таблицах Excel:

Надеюсь, это поможет

Функция nz в access

Есть таблица [ОбъемРабот] с полями:

[КодЦеха] — Длинное целое
[КодРаботы] — Длинное целое
[Январь] — Одинарное с плавающей точкой
[Февраль] — Одинарное с плавающей точкой
.
[Декабрь] — Одинарное с плавающей точкой.

Месяца в таблице обозначают объем данной работы КодРаботы), выполненной данным цехом(КодЦеха) в данном месяце.

Необходимо подсчитать суммарный объем по каждой работе за год всего предприятия. Я делаю запрос:

SELECT
SUM(Январь+Февраль+. +Декабрь) AS [Объем],
КодРаботы
FROM
ОбъемРабот
GROUP BY
КодРаботы;

и получаю неправильный результат (некоторые значения равны нулю). В справке MS Access сказано, что «Функция Sum пропускает записи с полями, содержащим значения Null». Если дело в этом, то каким образом можно это обойти? Или запрос составлен неверно?

> Если дело в этом, то каким образом можно это обойти?
SUM(iif(IsNull([Январь]),0,[Январь])+. )
или
SUM([Январь])+SUM([Февраль])+.

← →
Е. ( 2006-01-14 10:08 ) [2]

Тот же результат.

← →
Е. ( 2006-01-14 10:09 ) [3]

Тот же результат во втором варианте.

← →
Е. ( 2006-01-14 10:37 ) [4]

А вот первый вариант работает, правда несколько громоздко выглядит, но работает. Спасибо.
PS. Остается ощущение, что можно как-то упростить. Или нет?

← →
Е. ( 2006-01-14 10:45 ) [5]

Интересно, функция SUM везде так странно работает или только в MS Access?

> Тот же результат во втором варианте.
по идее не должно
приведи второй вариант полностью, как ты его написал.

> Интересно, функция SUM везде так странно работает или только в MS Access?
это не SUM это правила Null -> 1 + Null = Null (и вообще любое число + Null)
будет везде аналогично, правда с исключениями. (в MSSQL например это поведение настраиваемо, по умолчанию также (на тех установках mssql что я делал))

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

«неправильный результат» это пропуски да? (пустые значения). если да то все верно, и второй тебе не подойдет.

← →
Е. ( 2006-01-14 11:09 ) [9]

> sniknik © (14.01.06 11:01) [8]
> а понял. у тебя же там группировка, т.е. SUM-мируются небольшие
> группы значений, и наверное попадает что вся группа по какомуто
> месяцу из значений Null . тогда и вся сумма Null и сложение
> сумм где есть хоть одна с Null даст Null .
>
> «неправильный результат» это пропуски да? (пустые значения).
> если да то все верно, и второй тебе не подойдет.

Да именно так. Еще раз спасибо.

Это не важно, даже в пределах одной записи результат будет NULL если хотя бы одно из полей NULL
Только первый вариант даст нужный результат, преобразование NULL в 0

Е. (14.01.06 10:37) [4]
А ты столбиком запиши, будет и красиво и понятно.

Anatoly Podgoretsky © (14.01.06 12:51) [10]
> Это не важно, даже в пределах одной записи результат будет NULL если хотя бы одно из полей NULL
так именно это я и сказал. сумма в «линейку» дает Null .
но т.к. в [6]/[8] рассматривался второй вариант (SUM([Январь])+SUM([Февраль])+. ) то тут сначала складываются «столбики» и до строки Null «дойдет» только если весь столбец из Null. (в «столбиках» Null просто пропускается)

> Только первый вариант даст нужный результат, преобразование NULL в 0
если много значений и гарантированно хоть одно поле каждого столбца проинициализировано (есть значение), то и второй вариант сработает.

p.s. ктото когото не понял.

← →
Е. ( 2006-01-15 11:12 ) [12]

Нашлась некая функция Nz в MS Access, с ней запрос выглядит «красивее и понятнее»:

SELECT
SUM(Nz(Январь)+Nz(Февраль)+..+Nz(Декабрь)) AS Объем,
КодРаботы
FROM
ОбъемРабот
GROUP BY КодРаботы;

. но работает почему-то только непосредственно в Access. Если использовать через ADOQuery.Sql выдает ошибку: «Неопределенная функция «Nz» в выражении.» Почему?

> . но работает почему-то только непосредственно в Access.
т.к. это функция самого приложения (Application), а не jet (он только встроенный бейсик может использовать, но не функции описаные в разных «левых» программах)

иллюстрация
unit AccessAppUnit;

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, AccessXP, ComObj;

type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
Label1: TLabel;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
TestNull: OleVariant;
public
end;

var
Form1: TForm1;

procedure TForm1.FormCreate(Sender: TObject);
begin
TestNull:= Null;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
appAccess: _Application;
begin
appAccess:= CreateComObject(CLASS_AccessApplication) as _Application;
try
Label1.Caption:= appAccess.Nz(TestNull, «NULL»);
finally
appAccess:= nil;
end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
TestNull:= «NOT NULL»;
end;

0 0 голоса

Рейтинг статьи

Функция nz в access

Программирование на VBA Access 2003 – Встроенные функции

Встроенные функции VBA Access

Функция Nz

Замечания

Типичные тормоза запросов

Используйте SQL, а не VBA

Is Null, не IsNull()

IIf(), не Nz()

Доменные агрегатные функции

Составляйте выражения, чтобы использовать индексы

Оптимизируйте групповые запросы

WHERE против HAVING

FIRST против GROUP BY

Прочие приемы оптимизации

Функция MS Access Nz() не распознана в MS Excel

3 Ответа

Похожие вопросы:

Функция nz в access