параметр в запросе можно использовать
1С 8.3 Параметры в запросе
&НаСервере
Процедура ПередачаПараметровПростыхТиповВЗапросе ()
// Создание отбора по поступлению материала за 2020 год
Запрос = Новый Запрос ( «ВЫБРАТЬ
| Ссылка
|ИЗ
| Документ.ПоступлениеМатериалов
|ГДЕ
| Дата МЕЖДУ &НачДата И &КонДата
|УПОРЯДОЧИТЬ ПО
| Дата ВОЗР» );
&НаСервере
Процедура ПередачаПараметровСсылочныхТиповВЗапросе ()
// Создание отбора по материалам с единицей измерения «Куб.см.»
Запрос = Новый Запрос ( «ВЫБРАТЬ
| Наименование,
| ЕдиницаИзмерения
|ИЗ
| Справочник.Материалы
|ГДЕ
| ЕдиницаИзмерения = &ЕдинИзмер» );
&НаСервере
Процедура ПередачаПараметровСписочногоТипаВЗапросе ()
// Создание отбора по материалам, единицы измерения входят в переданный список
Запрос = Новый Запрос ( «ВЫБРАТЬ
| Наименование,
| ЕдиницаИзмерения
|ИЗ
| Справочник.Материалы
|ГДЕ
| ЕдиницаИзмерения В (&СписокЕдиницИзмерения)» );
&НаСервере
Процедура ПередачаПараметраВВидеТаблицыЗначенийВЗапросе ()
Запрос = Новый Запрос ( «ВЫБРАТЬ
| Наименование,
| СрокИспользования,
| Производитель
|ИЗ
| Справочник.Материалы
|ГДЕ
| (СрокИспользования, Производитель) В (&СписокСочетаний)» );
&НаСервере
Процедура ИспользованиеТаблицыЗначенийПереданнойВЗапросКакПараметр ()
// Сперва выбираем данные во временную таблицу, а потом работаем как с обычной таблицей
Запрос = Новый Запрос ( «ВЫБРАТЬ
| Название,
| РынЦена
|ПОМЕСТИТЬ
| ВременнаяТаблица
|ИЗ
| &ТаблицаДрагМеталлов КАК ДрагМеталлы
|;
|ВЫБРАТЬ
| Название,
| РынЦена
|ИЗ
| ВременнаяТаблица
|УПОРЯДОЧИТЬ ПО
| РынЦена УБЫВ» );
MS Access. Работа с данными при помощи запросов
Понятие запроса. Основные типы запросов
Создание запросов в режиме конструктора
Вычисления в запросах
Параметрические запросы
Перекрестный запрос
Запрос на изменение
Запросы в СУБД Access
Запрос — объект базы данных, который используется для извлечения информации из одной или нескольких таблиц или для выполнения определенных действий с данными.
По способу формирования запросы можно разделить на два вида:
В действительности любой запрос в Microsoft Access реализуется с помощью языка SQL. И хотя большинство запросов можно создавать в режиме конструктора, используя возможности запроса по образцу, каждый созданный запрос хранится в виде инструкции SQL. При создании запроса по образцу Microsoft Access автоматически формирует соответствующий SQL-запрос. Можно просмотреть инструкцию SQL для существующего запроса и внести в нее изменения. В этом случае автоматически будет обновляться определение соответствующего запроса по образцу в режиме конструктора.
По результатам действий и особенностям выполнения запросы можно разделить следующим образом:
Создание запросов в режиме конструктора
Для создания запросов по образцу используется режим конструктора. После определения таблиц (или запросов), на основе которых будет создаваться новый запрос, он будет открыт в режиме конструктора. Окно конструктора запросов состоит из двух частей.
В верхней части окна находятся списки полей тex таблиц или запросов, на основе которых строится данный запрос, в нижней части окна располагается бланк запроса.
Каждая строка бланка запроса выполняет определенную функцию:
Первым шагом построения запроса является выбор полей. Необходимые поля можно выбрать несколькими способами:
Иногда требуется включить в запрос все поля исходной таблицы. Для этого необходимо сделать двойной щелчок мышыо по строке заголовка соответствующего списка полей, выделяя таким образом сразу все поля, и перенести их одновременно в бланк запроса. При этом каждое поле будет помещено в отдельный столбец. Можно воспользоваться другим способом. В начале каждого списка полей, приведенных в верхней полонине окна, находится символ *, который означает «все поля». Для того чтобы включить в запрос все поля таблицы, можно просто перенести * в бланк запроса. В этом случае имя поля в бланке запроса будет содержать имя таблицы, за которым следует точка, а затем — символ * (например, Заказы.*), что означает выбор всех полей исходной таблицы.
У метода переноса * есть одно существенное достоинство. Если в структуре исходной таблицы производятся какие-либо изменения, например, добавляется новое поле, запрос будет автоматически изменен (при использовании первого способа запрос выбирает только те поля, которые были перенесены в бланк запроса. Однако если необходимо определить условие отбора, придется дополнительно добавить отдельные поля в бланк запроса, а чтобы они дважды не включались в выберу снять для соответствующих полей флажки Вывод на экран.
При выполнении запроса в качестве заголовков столбца динамической таблицы используются имена выбранных полей. Для переименования поля в результирующей динамической таблице необходимо установить текстовый курсор перед именем поля в строке Поле бланка запроса и ввести новое имя с двоеточием. В результате получится следующая конструкция: Новое имя: Старое имя поля.
Обычно Microsoft Access выводит записи в том порядке, в каком они выбираются из базы данных. Можно изменить последовательность вывода данных, определив ее порядок в строке Сортировка. При сортировке по нескольким полям порядок обработки полей определяется их положением в бланке запроса: сначала сортируются значения в крайнем левом поле и далее слева направо.
По умолчанию Microsoft Access выводит все поля, вклкюченные в бланк запроса. Если поле используется только для оп ределения условия выбора данных, для него необходимо снять флажок в строке Вывод на экран, щелкнув мышью в соотвествующей ячейке.
Выражение, которое указывает, какие записи необходимо включить в динамическую таблицу при выполнении запроса вводится в строку Условие отбора для поля, по которому это условие необходимо проверить. Для задания условий отбора можно использовать операторы меньше ( ), больше или равно (>=), не равно (<>), равно (=), Like (выбор по маске), Between (между), In (в интервале), And (и), Or (или) и другие, а также имена обьектов, константы и функции.
Завершение ввода условия выполняется нажатием клавиши Enter или просто переходом к другой ячейке бланка запроса с помощью клавиш управления курсором или мыши. Мicrosoft Access проводит синтаксический анализ заданного выражения. Например, если было введено значение текстового поля то это выражение будет представлено в кавычках. Если выражение не содержит никакого оператора, Microsoft Access будет исходить из того, что подразумевается оператор = или Like.
Допускается использование нескольких условий отбора, которые можно задать как для разных полей, так и для одного поля. Для создания сложных условий выбора данных используются логические операторы And и Or. Если условия отбора связаны оператором And, запись выбирается только в случае выполнения всех условий. Если же условия отбора связаны оператором Or, запись выбирается при выполнении хотя бы одного из всех условий. При определении нескольких условий отбора, связанных оператором And, для различных полей необходимо просто задать условие в строке Условие отбора для каждого из полей, образующих критерий выбора данных. Если же при определении нескольких условий поместить их в различные строки — строку Условие отбора и строку или — Microsoft Acces будет использовать Or-связь. В результате условия, расположенные в одной строке, связываются оператором And, в разных строках — оператором Or.
Т.о., при формировании условия отбора в запросах могут использоваться:
Подстановочные символы:
Использование параметров для ввода данных при выполнении запроса
Чтобы настроить в запросе к базе данных рабочего стола Access ввод условий при его выполнении, создайте запрос с параметрами. Это даст возможность использовать запрос повторно, не открывая его в Конструктор для изменения условий.
Примечание: Эта статья не относится к веб-приложениям Access.
Терминология
Прежде чем перейти к описанной ниже пошаговой процедуре, полезно ознакомиться с несколькими терминами.
Параметр. Параметр — это часть сведений, предоставляемых запросу при его выполнении. Параметры можно использовать отдельно или в составе длинных выражений для формирования условия запроса. Параметры можно добавить в запрос любого из следующих типов:
на создание таблицы;
Условия. Условия — это «фильтры», добавляемые в запрос для указания элементов, которые он должен возвратить.
Дополнительные сведения об указанных выше типах запросов см. в статье Знакомство с запросами.
Создание запроса с параметрами
Создание параметра аналогично добавлению обычного условия в запрос:
Создайте запрос на выборку и откройте его в конструкторе.
В строке «Условия» поля, к которым вы хотите применить параметр, введите текст, который вы хотите отобразить в поле параметра, в квадратных скобках. Например: [Введите дату начала:]
Повторите шаг 2 для каждого поля, в которое необходимо добавить параметры.
При запуске запроса текст отображается без квадратных скобок.
Введите нужное значение и нажмите кнопку ОК.
В условии можно использовать несколько параметров. Например, выражение Between [Введите дату начала:] And [Введите дату окончания:] при выполнении запроса создаст два поля.
Указание типов данных для параметра
Можно настроить параметр так, чтобы он принимал только определенный тип данных. Это особенно важно для числовых и денежных данных, а также данных даты и времени, так как в таком случае пользователи получат более содержательное сообщение об ошибке при вводе неправильного типа данных, например при вводе текста вместо денежного значения.
Примечание: Если параметр настроен на прием текстовых данных, любое введенное значение будет распознаваться как текст, а сообщение об ошибке не будет выводиться.
Чтобы задать тип данных для параметра в запросе, выполните указанные ниже действия.
Когда запрос открыт в конструкторе, на вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.
В диалоговом окне Параметры запроса в столбце Параметр введите текст запроса на ввод каждого из параметров, для которых требуется указать тип данных. Проверьте, соответствуют ли параметры запросам на ввод, указанным в строке Условия в бланке запроса.
В столбце Тип данных выберите тип данных для каждого параметра.
Добавление параметра в запрос на объединение
Так как запрос на объединение нельзя просмотреть в бланке запроса, действия с ним будут немного отличаться.
Откройте запрос на объединение в режиме SQL.
Добавьте к нему предложение WHERE, содержащее поля, в которые нужно добавить параметры.
Если предложение WHERE уже существует, проверьте, включены ли в него все поля, в которые нужно добавить параметры. Если нет, добавьте недостающие поля.
Введите запрос параметра в предложение WHERE, например WHERE [StartDate] = [Введите дату начала:].
Имейте в виду, что во все разделы запроса необходимо добавить одинаковые фильтры. На рисунке выше запрос содержит два раздела (разделенные ключевым словом UNION), поэтому параметр нужно добавить дважды. Но при выполнении запроса ввод данных запрашивается только один раз (предполагается, что во всех разделах указан одинаковый запрос).
Объединение параметров с помощью подстановочных знаков для большей гибкости
Как и в случае обычных условий, вы можете объединить параметры с помощью ключевого слова Like и подстановочных знаков для поиска соответствий в более широком диапазоне элементов. Предположим, что нужно запросить страну или регион и при этом сопоставить их со значениями, которые содержат строку параметра. Для этого выполните указанные ниже действия.
Создайте запрос на выборку и откройте его в конструкторе.
В строке Условия поля, в которое нужно добавить параметр, введите Like «*»&[, текст сообщения, а затем ]&»*».
При запуске запроса с параметрами запрос появляется в диалоговом окне без квадратных скобок и без ключевого слова Like или
поддиаметров: 
После ввода параметра запрос возвратит значения, содержащие строку параметра. Например, строка параметра us возвратит элементы, в которых поле параметра имеет значение «Австралия» или «Австрия».
Дополнительные сведения о подстановочных знаках см. в статье Использование подстановочных знаков в качестве условий.
Возврат элементов, не соответствующих параметру
Вместо возврата элементов, соответствующих параметру, можно создать запрос, возвращающий элементы, которые ему не соответствуют. Например, может потребоваться запросить год и возвратить элементы со значением года, большим чем указанное. Для этого введите оператор сравнения слева от запроса параметра в квадратных скобках, например >[Введите год:].
Видео: использование параметров в запросах
Использовать в запросе параметр не сложнее, чем создать запрос на основе условий. Запрос можно настроить таким образом, чтобы предлагалось ввести определенное значение, такое как артикул товара, или несколько значений, например две даты. Для каждого параметра запрос выводит отдельное диалоговое окно, в котором предлагается ввести значение.
В этом видео подробнее рассказывается о создании параметров в запросах.
Использование параметров в запросах, формах и отчетах
Если вам необходимо, чтобы запрос в Access при запуске каждый раз предлагал ввести параметры, создайте запрос с параметрами.
Вы также можете создать форму для ввода значений, позволяющих ограничить количество возвращаемых записей в запросах, формах и отчетах. В этой статье рассказывается, как с помощью форм сделать проще и удобнее работу с параметрами в запросах, формах и отчетах.
В этой статье
Обзор
С помощью условий в запросе с параметрами в Access можно ограничить набор записей, возвращаемых запросом. Функциональности диалоговых окон в запросе с параметрами может оказаться недостаточно. В этом случае можно создать форму, которая лучше подойдет для ввода параметров в вашей ситуации. В этой статье рассказывается, как настроить форму для ввода параметров запросов и отчетов. Здесь предполагается, что вы уже знакомы с принципами создания запросов и определения параметров в них. Прежде чем продолжать, ознакомьтесь как минимум с процедурой создания запроса на выборку.
В этой статье приведены примеры использования параметров в запросах. Она не содержит полной справочной информации о настройке условий.
Дополнительные сведения о создании запроса на выборку см. в статье Создание простого запроса на выборку.
Дополнительные сведения о запросах в целом см. в статье Знакомство с запросами.
Дополнительные сведения о создании параметров в запросах см. в статье Использование параметров для ввода данных при выполнении запроса.
Дополнительные сведения о настройке условий в запросах см. в статье Примеры условий запроса.
Использование параметров в запросах
Использовать в запросе параметр не сложнее, чем создать запрос на основе условий. Запрос можно настроить таким образом, чтобы предлагалось ввести определенное значение, такое как артикул товара, или несколько значений, например две даты. Для каждого параметра запрос выводит отдельное диалоговое окно, в котором предлагается ввести значение.
Добавление параметра в запрос
Создайте запрос на выборку и откройте его в конструкторе.
В строке Условия поля, для которого вы хотите применить параметр, введите в квадратных скобках текст, который должен отображаться в соответствующем диалоговом окне. Пример:
При выполнении запроса с параметрами этот текст отобразится в диалоговом окне без квадратных скобок.
При запросе параметров также можно использовать выражения. Пример:
Between [Дата начала] And [Дата окончания]
Примечание: Для каждого параметра выводится отдельное диалоговое окно. Во втором примере появляется два окна: одно для даты начала, второе — для даты окончания.
Повторите шаг 2 для каждого поля, для которого хотите добавить параметры.
С помощью описанных выше действий можно добавить параметр в запрос любого из следующих типов: выборка, добавление, создание таблицы и обновление, а также перекрестный запрос.
Кроме того, с помощью перечисленных ниже действий можно добавить параметр в запрос на объединение.
Откройте запрос на объединение в режиме SQL.
Добавьте предложение WHERE, содержащее каждое поле, для которого требуется ввести параметр.
Если предложение WHERE уже существует, убедитесь в том, что поля, для которых нужно ввести параметры, уже есть в этом предложении; в противном случае добавьте их.
Вместо условия в предложении WHERE используйте запрос параметра.
Указание типов данных для параметра
Вы также можете указать, данные какого типа разрешается вводить в качестве значения параметра. Тип данных можно настроить для любого параметра, но особенно важно сделать это для числовых и денежных данных, а также значений даты и времени. Когда для параметра указан тип данных, пользователи получают более осмысленные сообщения об ошибках в случае ввода значений неправильного типа (например, текста вместо денежных величин).
Примечание: Если параметр настроен для ввода текстовых данных, любое введенное значение будет распознаваться как текст, а сообщение об ошибке не будет выводиться.
Чтобы задать тип данных для параметра в запросе, выполните указанные ниже действия.
Когда запрос открыт в конструкторе, на вкладке Конструктор в группе Показать или скрыть нажмите кнопку Параметры.
В диалоговом окне Параметры запроса в столбце Параметр введите текст запроса на ввод значения для каждого параметра, для которого требуется указать тип данных. Убедитесь, что каждый из параметров соответствует запросу, который используется в строке Условие отбора в бланке запроса.
В столбце Тип данных выберите тип данных для каждого параметра.
Создание формы для ввода параметров
Хотя в запросах с параметрами есть встроенные диалоговые окна для ввода значений, они предлагают лишь простейшие функции. В то же время форма для ввода параметров поддерживает перечисленные ниже дополнительные возможности.
Использование элементов управления для ввода данных определенных типов, например календарей для дат.
Сохранение введенных параметров для использования в других запросах.
Списки и поля со списками для ввода параметров, позволяющие выбирать значения из заранее заданных наборов.
Элементы управления для выполнения других функций, таких как открытие запроса или обновление данных в нем.
В приведенном ниже видео демонстрируется создание простой формы для ввода параметров запроса вместо стандартных диалоговых окон.
Создание формы для ввода параметров для отчета
Реализовать этот сценарий можно несколькими способами, но мы рассмотрим вариант, в котором активно используются макросы. Создать форму для сбора параметров отчета можно с помощью описанной ниже последовательности действий.
Шаг 1. Создайте форму для ввода значений
На вкладке Создание в группе Формы нажмите кнопку Конструктор форм.
В режиме конструктора нажмите F4, чтобы открыть страницу свойств, и задайте свойства формы, как показано в таблице ниже.
Введите имя, которое будет отображаться в строке заголовка формы.
Режим по умолчанию
Режим сводной таблицы
Режим сводной диаграммы
Для каждого параметра, который будет вводиться с помощью формы, на вкладке Конструктор в группе Элементы управления нажмите кнопку Поле.
Задайте для полей свойства, как показано в таблице ниже.
Введите имя, описывающее параметр, например ДатаНачала.
Выберите формат, который соответствует типу данных поля параметра. Например, для поля даты выберите вариант Полный формат даты.
Сохраните форму и задайте для нее название, например frmCriteria.
Шаг 2. Создайте блок кода, проверяющий, загружена ли форма параметров
На вкладке Создание в группе Макросы и код нажмите кнопку Модуль.
В редакторе Visual Basic откроется новый модуль.
Вставьте в редакторе следующий код:
Сохраните модуль под уникальным именем и закройте редактор Visual Basic.
Шаг 3. Создайте макрос, управляющий работой формы и отчета
С помощью функций в макроса Access можно определить все необходимые действия, которые необходимо предпринять в одном макросе. Мы создадим четыре вуа макроса — «Открыть диалоговое окно», «Закрыть диалоговое окно», «ОК» и «Отмена» — для управления задачами, необходимыми для выполнения этой процедуры. С помощью приведенного ниже снимка экрана создайте новый макрос со следующими макросами и макросами. В данном примере наша форма параметров называется frmCriteria. Настройте макрос в соответствие с именем созданной ранее формы. Кроме того, для просмотра всех макроса необходимо нажать кнопку «Показать все действия» на вкладке «Конструктор».
Сохраните и закройте макрос. Задайте для макроса имя, например Макрос диапазона дат.
Шаг 4. Добавьте на форму кнопки «ОК» и «Отмена»
Снова откройте форму параметров, которую создали в режиме конструктора.
На вкладке Конструктор в группе Элементы управления отмените выбор параметра Использовать мастера.
На вкладке Конструктор в группе Элементы управления щелкните Кнопка.
Расположите курсор под полями на форме и мышью создайте кнопку ОК.
Если окно свойств не отображается, откройте его, нажав клавишу F4.
Задайте для кнопки ОК свойства согласно приведенной ниже таблице.
Введите имя макроса, например Макрос диапазона дат.ОК.
Создайте кнопку Отмена и задайте для нее свойства, как показано в таблице ниже.
Введите имя макроса, например Макрос диапазона данных.Отмена.
Сохраните и закройте форму.
Шаг 5. Используйте значения из формы в качестве условий запроса
Откройте запрос, который создали в режиме конструктора.
Введите условия для данных. Используйте объект Forms, имя формы и имя элемент управления:
Например, в базе данных Access (ACCDB или MDB) для формы frmCriteria можно задать следующее выражение, ссылающееся на элементы управления «Дата начала» и «Дата окончания» в запросе:
Between [Формы]![frmCriteria]![Дата начала] And [Формы]![frmCriteria]![Дата окончания]
Шаг 6. Добавьте макрокоманды в события отчета
Откройте нужный отчет в режиме конструктора.
Если окно свойств не отображается, откройте его, нажав клавишу F4.
Убедитесь, что для свойства Источник записей в отчете используется запрос с параметрами, который мы создали.
Задайте два дополнительных свойства отчета, как показано в таблице ниже.
Введите имя макроса, например Макрос диапазона дат.Открыть диалоговое окно.
Введите имя макроса, например Макрос диапазона дат.Закрыть диалоговое окно.
При открытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе «Открыть диалоговое окно» объекта «Макрос диапазона дат». И аналогично: при закрытии отчета Access будет выполнять команды, содержащиеся во вложенном макросе «Закрыть диалоговое окно» объекта «Макрос диапазона дат».
Сохраните и закройте отчет.
Шаг 7. Проверьте форму в действии
Теперь, когда все объекты Access созданы, можно проверить, как они работают. Откройте отчет в режиме отчета или представлении для печати и обратите внимание, что перед показом отчета Access отображает форму ввода параметров в диалоговом режиме. Введите необходимые условия в созданные вами текстовые поля и нажмите кнопку ОК на форме. После этого Access скроет форму (вывод на экран — «Нет») и отобразит отчет, содержащий только те данные, которые соответствуют вашим условиям. Это произойдет благодаря тому, что запрос с параметрами, на котором основан отчет, использует значения в элементах управления на скрытой форме. Когда вы закроете отчет, Access также закроет форму параметров.