Запросы с фильтрацией
Возможно, Вы обратили внимание на то, как я решал данную задачу по организации поиска нужных мне данных, - вначале получил весь набор записей, а затем организовал их фильтрацию средствами VBA. Возможен и другой способ, в ряде случаев более предпочтительный, - он состоит в том, чтобы фильтрацию выполнять в момент чтения набора записей, задав соответствующим образом оператор SELECT языка SQL. Оператор Select имеет в своем арсенале конструкцию Like, позволяющую задать шаблон поиска и тем самым проводить фильтрацию непосредственно при чтении записей, что может приводить к существенному уменьшению объема набора записей, передаваемого клиенту.
Давайте подробно рассмотрим этот вариант работы. Полагаю, это будет полезным, как с содержательной, так и с программистской точки зрения, поскольку, например, я сам не сразу разобрался, как корректно следует на VBA задавать конструкцию Like.
Начнем повторно наше рассмотрение с того момента, когда после задания критериев поиска пользователь нажимает кнопку "Найти" в форме LookCustomer. Я изменил код обработчика события Click для этой кнопки следующим образом:
Private Sub CommandButton1_Click() 'Найти заказчика по заданным реквизитам 'LookingFor Version2LookingFor End Sub
Как видите, вместо ранее вызываемой процедуры LookingFor я буду вызывать ее версию, которую и рассмотрим подробно. Начнем с текста:
Public Sub Version2LookingFor() 'Найти заказчика по заданным реквизитам If (LookCustomer.TextBox1 <> "") Or (LookCustomer.TextBox2 <> "") Or _ (LookCustomer.TextBox3 <> "") Or (LookCustomer.TextBox4 <> "") Or _ (LookCustomer.TextBox5 <> "") Then 'Критерии поиска заданы. 'Спрятать форму. LookCustomer.Hide 'Создать набор отфильтрованных записей с реквизитами заказчиков. CreateFiteredCustomers 'Сформировать список заказчиков, удовлетворяющих критериям поиска. FormListSelectedCustomers If SelectedCustomers.ListBox1.ListCount > 0 Then 'Найдены заказчики, удовлетворяющие критериям. SelectedCustomers.Show Else 'Показ всех заказчиков. MsgBox ("Нет записей, удовлетворяющих заданным критериям!" _ & " Будут показаны все заказчики!") Choose End If Else MsgBox ("Задайте значение хотя бы в одном поле!") End If End Sub
Я не буду подробно описывать работу этой процедуры, поскольку она, во многом, похожа на своего двойника - процедуру LookingFor. Главное отличие с программной точки зрения состоит в том, что вместо относительно простой процедуры CreateCustomers здесь вызывается более сложная процедура CreateFilteredCustomers. С содержательной точки зрения отличие состоит в том, что первая процедура создает полный набор всех заказчиков, а вторая - набор заказчиков, удовлетворяющих условию фильтра.
Приведу программный код процедуры CreateFilteredCustomers:
Public Sub CreateFiteredCustomers() 'Создание и выполнение команды, 'позволяющей получить данные о заказчиках, 'удовлетворяющих фильтру Dim strSQL1 As String 'Вызов функции FormSQLStatement, формирующей строку SQL strSQL1 = FormSQLStatement 'задание объекта Command Cmd1.CommandText = strSQL1 'вызов команды на исполнение методом Execute Set Rst1 = Cmd1.Execute 'Перенос данных из набора записей в список SelectedCustomers.ListBox1.Clear RowIndex = 0 With Rst1 .MoveFirst Do While Not .EOF 'Текущая запись переносится в список 'Первый столбец SelectedCustomers.ListBox1.AddItem .Fields(1) 'Остальные столбцы On Error Resume Next For i = 1 To ColumnCount - 1 txt = "" txt = .Fields(i + 1) SelectedCustomers.ListBox1.Column(i, RowIndex) = txt Next i RowIndex = RowIndex + 1 .MoveNext Loop End With End Sub
И в этой процедуре немного нового. Она построена по образцу уже приведенных выше процедур. Схематично действия, выполняемые в ней следующие: формируется строка SQL, задающая текст команды, создается команда - объект Command, команда выполняется, данные из набора записей, полученного в результате выполнения команды, переносятся в поля списка формы SelectedCustomers.
То новое, на что хочу обратить внимание, связано с формированием строки SQL. Как положено, я написал специальную функцию FormSQLStatement, которая и решает поставленную задачу. Именно она и интересует нас в первую очередь. Вот ее текст:
Public Function FormSQLStatement() As String 'Возвращает SQL оператор, фильтрующий записи Dim strSQL As String strSQL = "Select * FROM [Заказчики] WHERE " Dim txt As String Const Кавычка = "'" txt = LookCustomer.TextBox1.Text If txt <> "" Then strSQL = strSQL & "[Название] Like " & _ Кавычка & "%" & txt & "%" & Кавычка & " Or " txt = LookCustomer.TextBox2.Text If txt <> "" Then strSQL = strSQL & "[Адрес] Like " & _ Кавычка & "%" & txt & "%" & Кавычка & " Or " txt = LookCustomer.TextBox3.Text If txt <> "" Then strSQL = strSQL & "[Город] Like " & _ Кавычка & "%" & txt & "%" & Кавычка & " Or " txt = LookCustomer.TextBox4.Text If txt <> "" Then strSQL = strSQL & "[Телефон] Like " & _ Кавычка & "%" & txt & "%" & Кавычка & " Or " txt = LookCustomer.TextBox5.Text If txt <> "" Then strSQL = strSQL & "[Директор] Like " & _ Кавычка & "%" & txt & "%" & Кавычка & " Or " 'Удалить последние пять символов - Or strSQL = Left(strSQL, Len(strSQL) - 4) FormSQLStatement = strSQL End Function
Поговорим подробнее о работе этой функции. Наша цель состоит в том, чтобы задать сложное выражение WHERE оператора Select, задающее фильтр. По условиям задачи это выражение состоит из нескольких слагаемых - логических условий, соединенных связкой OR (ИЛИ). Число слагаемых формируется динамически и может быть от одного до пяти, в зависимости от того, сколько полей заполнил пользователь в форме LookCustomer. Напомню, что хотя бы одно поле он обязан задать, чтобы можно было говорить о ключе поиска. Все слагаемые формируются по одной схеме, - если задано соответствующее ключевое поле в форме, то формируется и соответствующее ему слагаемое.
Каждое условие задается шаблоном, заданным с помощью конструкции Like, и имеет следующий вид - %текст%. Этот шаблон соответствует любой строке, содержащей вхождение строки текст в качестве подстроки. Символы шаблона %, окаймляющие подстроку текст, указывают на произвольный префикс и произвольное окончание искомой строки.
Вот, наверное, и все, что хотелось сказать о формировании строки фильтра. Но прежде, чем закончить эту тему сделаю два замечания, связанные с шаблонами.
Замечание 1. Шаблоны не новость ни в математике, ни в программировании. Традиционно, символ * (звездочка) в шаблоне соответствует произвольной строке символов, а символ ? (знак вопроса) соответствует произвольному одиночному символу. Мне совершенно непонятно, почему в шаблонах на VBA нужно было изменять традиции и использовать другие символы - знак %(процент) вместо звездочки и знак _ (подчеркивание) вместо знака вопроса. Это тем более странно, что при формировании запроса с выражением Like непосредственно в Access используются традиционные символы.
Замечание 2. Экспериментируя с шаблонами, я наткнулся на одну ошибку в Access, связанную, видимо, с локализацией. Ошибка проявляется в следующей ситуации. Пусть в Access с помощью конструктора строится запрос по таблице, у которой первое поле является ключевым и содержит, скажем, название организации. Предположим, что на это поле накладывается фильтр типа Like - S*, где S - буква русского алфавита. Этот фильтр, естественно, означает, что запросу удовлетворяю организации, начинающиеся с буквы S. Так вот, все работает правильно за исключением, когда в качестве начальной буквы задается "Г" или "К". Замечу, что в других полях шаблон "Г*" работает правильно.
