Краткое содержание 29 Об этих стрелках 30 о сочетаниях клавиш 32 о щелчках кнопкой мыши 33 Примеры 33
 Скачать 19.64 Mb.
|
|
Подсказка Вам нужно больше свободного места для набора длинного выражения? Можно расширить любой столбец в Конструкторе запросов для увеличения видимой зоны, но в случае сложных вычислений этого все равно не хватит. Лучше щелкнуть кнопкой мыши в области Поле и нажать сочетание клавиш Выражения с текстовыми значениями Несмотря на то, что обычно вычисляемые поля имеют дело с числовыми данными, но так бывает не всегда, существуют действительно удобные способы обработки текста. Если у вас есть текстовые данные, конечно нельзя применять сложение, вычитание и другие арифметические операции. Но можно слить текст. Есть возможность связать несколько полей с адресной информацией и отображать их все в одном поле, экономя пространство (и, возможно, облегчая экспорт этих данных и другую программу). Для слияния текста применяется оператор амперсанд (&). Далее показано, как создать поле FullName (ФИО), в котором собрана информация из полей FirstName и LastName: FullName: [FirstName] & [LastName] Это выражение выглядит достаточно корректным, но на самом деле у него есть один недостаток. Поскольку вы не вставили никаких пробелов, имя и фамилия в результате прижаты друг к другу, например, так: BenJenks. Лучше слить три фрагмента текста: имя, пробел и фамилию. Вот исправленная версия: FullName: [FirstName] & " " & [LastName] Приведенное выражение создаст значение: Ben Jenks. Можно также поменять местами имя и фамилию и отделить их запятой, если вы предпочитаете указывать фамилию первой (например, Jenks, Ben) для облегчения сортировки: FullName: [LastName] & ", " & [FirstName] Примечание В программе Access есть два типа текстовых значений: те, которые вы извлекаете из других полей, и те, которые вы вводите непосредственно (или фиксированные). Когда вы вводите текстовые константы, такие как запятая или пробел в предыдущем примере, их следует заключать в кавычки, чтобы программа Access знала, где начинается и заканчивается текст. Вы даже можете применять амперсанд для сцепления текста с числовыми значениями. Если вы хотите, чтобы слегка бесполезный текст "The price is" появлялся перед каждым значением цены, примените следующее вычисляемое поле: Price: "The price is: " & [Price] Функции запросов Теперь, возможно, вам приходит в голову мысль о том, что манипулировать числами и текстом можно более искусными способами — способами, которые предоставляют гораздо больше возможностей, чем простые арифметические операции. Вам может потребоваться округление чисел или преобразование строчных букв в прописные. Программа Access действительно предоставляет средства, называемые функциями и позволяющие поднять ваши выражения на более высокий уровень. Функция — это реализация встроенного алгоритма, принимающая ваши данные, выполняющая вычисления и возвращающая результат. Разница между функциями и математическими операциями, как вы уже убедились на деле, заключается в том, что функции могут выполнять более сложные действия. У программы Access есть каталог с десятками разных функций, многие из которых реализуют такие приемы, которые вы даже не надеялись выполнить самостоятельно. Функции пригодятся для всех видов обработки данных в программе Access. Вы можете примять их в:
Изучив как следует мир функций, вы поймете, что многие из них хорошо подходят для вычисляемых полей, но не для условий отбора. В следующих разделах вы увидите, где именно разумнее всего использовать каждую из них. Примечание Функции — это встроенная часть версии языка SQL (см. разд. "Режим SQL" главы 6), применяемой в программе Access для обработки данных. Применение функций Применяете ли вы простейшую или сложнейшую функцию, синтаксис — правила применения функции в выражении — остается неизменным. Для использования функции просто введите ее имя с последующими скобками. Затем внутрь скобок поместите все данные, которые нужны функции для выполнения ее вычислений (если это необходимо). Для примера рассмотрим очень полезную функцию Round (), которая принимает число с дробной частью и убирает любые нежелательные позиции в дробной части. Эта функция — удобный способ приведения в порядок отображаемых значений вычисляемого поля. Вы оцените полезность функции Round (), если создадите выражение, подобное приведенному далее и вычисляющее цены, сниженные на 5%: SalePrice: [Price] * 0.95 Примените это выражение к цене $43.97 и получите в результате 41.7715, что вряд ли будет выглядеть уместно на ценнике товара. Здесь пригодится функция Round (). Передайте ей неокругленное число и число знаков в дробной части, которое нужно сохранить: SalePrice: Round([Price] * 0.95, 2) Технически функции Round ( ) требуется две порции информации или два аргумента. Первый — это число, которое округляется (вданном случае это результат вычисления Price * 0. 95), а второй — количество цифр, которое вы хотите оставить справа от десятичной точки (2). В результате получается значение, округленное до двух десятичных знаков, или 41.77. Примечание Большинству функций, подобных Round (), требуются два или три аргумента. Но некоторые функции могут принять гораздо больше, в то время, как нескольким функциям вообще не нужны аргументы Часто задаваемый вопрос Банковское округление Похоже, программа Access округляет числа неправильно. Как быть? Вас может удивить то, что Access округляет число 21.985 до 21.98. Если вас учили округлять до большего числа, числа заканчивающиеся цифрой 5, то вы считаете, что результат должен быть 21.99. Этот способ называют арифметическим округлением. Программа Access не применяет арифметическое округление — она выбирает банковское округление, которое лучше в некоторых случаях. Разница между арифметическим и банковским округлением заключается в трактовке цифры 5. Поскольку число 21.985 находится точно в середине, между числами 21.98 и 21.99, не просто решить, что с ним делать. При постоянном округлении числа с 5 на конце до большего числа вносится систематическое отклонение в итоги и средние значения. Поскольку вы округляете до большего чаще, чем до меньшего, любые итоги или среднее, которые вы вычисляете, получаются чуть больше, чем следовало бы. Банковское округление решает эту проблему округлением 5 в одних случаях до большего числа, а в других до меньшего, в зависимости от соседней четной или нечетной цифры. Число 21.985 округляется до меньшего числа 21.98, а число 21.995 — до большего, 22. Это не единственный способ борьбы с систематическим отклонением (можно решать случайным образом, когда округлять, а когда нет), но это общепринятая практика в бухгалтерских расчетах и статистике. Вложенные функции В вычисляемом поле или условии отбора можно применять несколько функций. Этот прием известен как вложенные функции: специальный термин, обозначающий вставку одной функции внутрь другой. Например, в программе Access есть встроенная функция определения абсолютного значения числа Abs ( ), преобразующая отрицательные числа в положительные (и оставляющая положительные числа без изменения). Далее приведен пример деления одного поля на другое и получения в результате заведомо положительного значения: Speed: Abs ([DistanceTravelled] / [TimeTaken]) Если полученный результат нужно округлить, можно поместить целиком все выражение внутрь скобок функции Round ( ): Speed: Round (Abs([DistanceTravelled] / [TimeTaken]), 2) Вычисляя выражение с вложенными функциями, программа Access сначала вычисляет результат самой внутренней функции. В данном примере, прежде всего, определяется абсолютное значение, а затем результат округляется. В приведенном далее примере порядок вычислений изменен на обратный без изменения результата: Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2) ) Во многих случаях порядок вложения функций имеет значение, и разный порядок приводит к различным результатам. Вложенные функции быстро превращают выражение в потенциально опасное. Даже в сравнительно простом примере вычисления скорости трудно предсказать результат без пошагового вычисления выражения. Одна не на месте стоящая или пропущенная скобка может привести к сбою вычисления. Если вы применяете вложенные функции, не пишите сразу выражение целиком — включайте функции в выражение поочередно и после вставки очередной функции выполняйте вычисление. Построитель выражений Функции — замечательное нововведение, но в программе Access может оказаться слишком много замечательных вещей. Программа предлагает перечень из десятков различных функций, связанных с решением разнообразных задач, некоторые из этих функций предназначены для выполнения специализированных математических и статистических вычислений. Примечание В этой книге описана далеко не каждая функция {если бы это было так, вам пришлось бы, сидя над книгой, бороться со сном). Но в следующих разделах вы познакомитесь с наиболее полезными функциями для работы с числами, тестом и датами. Для поиска дополнительных функций используйте Построитель выражений (Expression Builder) Если же вы предпочитаете интерактивный режим обучения, проверьте информативный Web-pecypc www.techonthenet.com/access/functions. Для быстрого поиска нужных вам функций Access предлагает компонент, именуемый Построителем выражений. Для его запуска выполните следующие действия:
Если вы создаете вычисляемое поле, нужно щелкнуть правой кнопкой мыши в ячейке Поле, Если создается условие отбора, следует щелкнуть правой кнопкой мыши в ячейке Условие отбора. П  осле выбора команды Построить на экране появляется окно Построителя выражений, отображающее текущее содержимое поля (рис. 7.3). Рис. 7.3. Построитель выражений состоит из текстового поля в верхней части окна, в котором можно редактировать выражение, кнопок быстрой вставки знаков обычных операций (таких как +, -, / и *, если почему-либо вы не можете их найти на клавиатуре), и трехпанельного обозревателя в нижней части окна, который поможет найти нужные поля и функции 3. Вставьте или отредактируйте выражение. У Построителя выражений есть два ускоряющих приема работы, которые вы, может быть, захотите испробовать. Можно вставлять имя без ввода с клавиатуры (рис. 7.4) и можно найти функцию с помощью обзора (рис. 7.5). Примечание Построитель выражений — универсальное средство создания выражений в вычисляемых полях и условиях отбора. Некоторые параметры имеют смысл только в одном из его назначений. Логические операторы, такие как символ равенства (=), And, Or, Not и Like, удобны для задания условий фильтрации (см. разд. "Построение условий отбора" главы 6), но бесполезны в вычисляемых полях.  Р  ис. 7.4. Для вставки имени поля щелкните дважды кнопкой мыши папку Таблицы в самом левом списке. Затем щелкните мышью вложенную папку, соответствующую нужной вам таблице. И, наконец, дважды щелкните кнопкой мыши имя поля в среднем списке для добавления его в ваше выражение. Этот прием рекомендуется только тем, кто любит щелкать кнопкой мыши Рис. 7.5. Для поиска функции начните с двойного щелчка кнопкой мыши папки Функции в левом списке. Затем выберите вложенную папку Встроенные функции. (Другие варианты отображают любые пользовательские функции, которые вы добавили в вашу БД с помощью пользовательского кода VBA.) Далее выберите категорию функции в среднем списке. В правом списке показаны все функции в выбранной категории. Вы можете дважды щелкнуть кнопкой мыши функцию для вставки ее в выражение Примечание Когда вы вставляете имена полей в Построителе выражений, они записываются в более длинном формате с обязательным указанием имени таблицы. Вы увидите [Products] ! [Price] вместо просто [Price]. He волнуйтесь — для программы Access это одно и то же. 4. Щелкните мышью кнопку ОК. Программа Access скопирует ваше новое выражение в ячейку Поле или Условие отбора. Примечание К  огда в Построителе выражений вставляется функция, программа добавляет заполнители (например, Рис. 7.6. В справке функции Round () описываются ее назначение и два параметра. Второй параметр — количество знаков в дробной части — заключен в квадратные скобки, что означает необязательное значение. Пропустите его, и программа Access округлит значение до ближайшего целого числа. Слева приведен перечень функций, который позволяет просмотреть любую другую функцию Access и прочесть ее описание Большинство специалистов Access считают Построитель выражений слишком тяжеловесным, чтобы использовать его в работе. Но несмотря на то, что это не самое эффективное средство написания выражений, построитель предоставляет прекрасный способ знакомства с новыми загадочными функциями, благодаря своей справке для встроенных функций. Если вы нашли функцию, которая кажется многообещающей, но хотите получить дополнительную информацию, выберите ее в списке и щелкните мышью кнопку Справка (Help). Вы будете вознаграждены кратким резюме, объясняющим назначение функции и описывающим ее параметры, которые нужно задать, как показано на рис. 7.6. Форматирование чисел Format ( ) — интересная математическая функция, преобразующая числа в текст. Она интересна, потому что создаваемый текст можно отформатировать несколькими способами, управляя, таким образом, представлением чисел. Для того чтобы понять разницу, вернемся к выражению, использованному ранее для снижения цены: SalePrice: [Price] * 0.95 Даже если у поля Price не Денежный тип, вычисленные значения в поле SalePrice (продажная цена) выводятся как обычные числа (без знака валюты, разделителя тысяч и т. д.). Вы увидите значение 43.2 вместо желаемого $43.20. Решить эту проблему можно, применив функцию Format ( ) для задания денежного формата вывода: SalePrice: Format([Price] / 0.95, "Currency") Теперь вычисленные значения содержат знак валюты. Более того, поскольку денежные суммы отображаются с двумя знаками после точки, вам не нужно больше применять функцию Round ( ). Хитрость применения функции Format () состоит в выборе текста, задаваемого в качестве второго аргумента для получения желаемого результата. В табл. 7.2. приведены возможные варианты. Таблица 7.2. Варианты форматирования
Таблица 7.2 (окончание)
Практические занятия для опытных пользователей. Улучшенные числовые форматы Истинные педанты не будут довольны вариантами, перечисленными в табл. 7.2. Им нужен полный контроль количества десятичных знаков в числе. Один из возможных вариантов — использовать функции FormatCurrency( ), FormatPercent ( ) и FormatNumber ( ) (в зависимости от необходимости вывода значения как денежного, процентного или обычного числового). В этих функциях в качестве первого аргумента задается число, которое нужно отформатировать, а в качестве второго — число десятичных знаков, которое нужно сохранить. Для более полного контроля можно определить собственный формат, точно описывающий то, что вы хотите получить, и затем применить его в функции Format (). В данной книге не рассматриваются пользовательские числовые форматы, по вы можете посмотреть дополнительную информацию в справочной системе программы Access (см. рис. 7.6). Дополнительные математические функции Математическим функциям в программе Access не уделяется должного внимания, потому что потребность в них возникает крайне редко. Вы уже видели функции Round ( ) и Format ( ) — самые полезные в этой категории — но есть еще несколько других (табл. 7.3), к которым знатоки Access обращаются время от времени в вычисляемых полях. Таблица 7.3. Функции для числовых данных
Таблица 7.3. (окончание)
Малоизвестная или недооцененная возможность. Использование случайных чисел для сортировки в случайном порядке Функцией Rnd ( ) пользуются редко — в конце концов, кому нужны столбцы, заполненные искусственно сгенерированными данными? Однако инициативные гуру Access предложили одно интригующее применение для функции Rnd ( ). Они применяют ее для сортировки таблицы, таким образом, чтобы записи выводились в случайном порядке. По существу вы добавляете вычисляемое поле, содержащее случайное число. Можно использовать вполе выражение, такое как Random: Rnd ( ). Если посмотреть результаты вашего запроса, то можно увидеть случайное значение в диапазоне от 0 до 1 (например, 0.7045, 0.2344 и т. д.) рядом с каждой записью. Теперь вернитесь в Конструктор и сбросьте флажок Вывод на экран для того, чтобы поле Random не выводилось на лист данных. Далее выберите порядок в ячейке Сортировка по возрастанию или по убыванию (что на самом деле не имеет значения) и снова выполните запрос. Ву-а-ля! Каждое выполнение запроса выводит на экран записи в разном порядке, в соответствии со случайными числами, которые программа Access генерирует на лету. Текстовые функции Все функции, которые вы видели до этого момента, работали с числовыми данными. Но с текстом тоже можно делать многое. В целом есть три способа обработки текста. ■ Слияние текста. Вы можете соединить несколько текстовых полей в одном. Для этого способа не нужна функция — достаточно оператора &, описанного в разд. "Выражения с текстовыми значениями "ранее в этой главе.
■ Замена строчных букв прописными и наоборот. Возможно, вы захотите отобразить строчные буквы прописными или наоборот. В табл. 7.4 перечислены функции, наиболее часто применяемые для обработки текста. Таблица 7.4. Функции для работы с текстом
С помощью этих функций вы можете создать вычисляемое поле, которое отображает фрагмент длинной текстовой строки или изменяет вид отображения (строчные или прописные буквы). Применение этих функций в условиях отбора не столь очевидно. Можно создать условие фильтрации, задающее совпадение с частью текстовой строки, а не со всей строкой. Далее приведен пример условия отбора, выбирающего записи, начинающиеся с "Choco": Left([ProductName], 5) = "Choco" На рис. 7.7 показано, как ввести это условие отбора. Функция Len ( ) — особый случай. Она проверяет текстовое значение и возвращает числовую информацию (в данном случае количество символов в строке, включая все пробелы, буквы, цифры и специальные символы). Эта функция не слишком полезна в простых вычисляемых выражениях, т. к. вас редко будет интересовать количество букв в текстовой строке. Но она позволяет создавать интересные условия отбора, включая, например, такое, которое отбирает все записи с полем Description короче 15 символов. Len{Description) < 15 Практические занятия для опытных пользователей. Как извлечь первое слово из текстовой строки Функции обработки строк созданы с ориентацией на символы. Они умеют подсчитывать символы, но не понимают, что такое слово пли предложение. Единственный способ обойти это ограничение — применить функцию Instr ( ), которая ищет один или несколько символов в текстовой строке. (Название Instr ( ) — это сокращение от "in string", поскольку вы ищите конкретные символы внутри текстовой строки.) Для поиска символов "he" с строке "Hi There" нужно применить функцию Instr ( ) следующим образом: Instr("Hi There", "he") Результат равен 5, потому что текст "he" начинается с пятой символьной позиции. Если программа Access не находит совпадения, функция Instr ( ) возвращает 0. Если же есть множественные совпадения, Instr () возвращает позицию первого. Сама по себе эта функция не очень полезна в условиях отбора и вычисляемых полях. Но ее можно использовать в сочетании с другими функциями, такими как Mid ( ) и Left ( ), для вырезания части строки, расположенной рядом с какой-либо буквой. Можно применить функцию Instr ( ) для поиска первого пробела и вырезать весь текст до этого пробела. Таким образом, вы извлечете целое слово. Далее приведено слегка ошеломляющее вычисляемое поле, которое получает первое слово из поля ProductName с помощью вложенных функций (см. разд. "Вложенные функции" ранее в этой главе). Оно разбито на несколько строк для того, чтобы разместить его на странице книги. Когда будете его набирать, поместите все выражение в одну строку. FirstWordProduct: Left([ProductName], Instr([ProductName], ""- 1)) Это выражение переводится следующим образом: "Найди позицию первого пробела, вычти единицу и извлеки все символы слева от пробела". Вычислите это поле для значения Banana Cream Fudge и вы получите вырезанный текст Banana, что выглядит как впечатляющий яркий трюк.  Рис. 7.7. Функции Left ( ), Right ( ) и Mid ( ) действуют во многом аналогично ключевому слову Like, помогая найти совпадения кусочков длинной текстовой строки Функции для обработки дат Вы уже видели, как можно использовать операции сложения и вычитания при работе с датами (см. разд. "Поля с датами "ранее в этой главе). Но вы можете выполнить гораздо больше действий с помощью некоторых функций Access для работы с датами. Несомненно, многие применяют функции Now () и Date (), с которыми вы познакомились в главе 4. Эти функции извлекают текущие дату и время или только текущую дату. Их можно применять в запросах, работающих с заказами, принесшими доход в текущем году. Вот условие для выбора просроченных проектов: = Вставьте его в ячейку Условие отбора поля DueDate (срок платежа) и вы увидите только те записи, в которых поле DueDate содержит дату, наступившую ранее нынешнего дня. Анализ дат может быть более сложным в сочетании с функцией DatePart (), которая извлекает часть информации из даты. DatePart () может определить номер месяца или год, позволяя игнорировать другие подробности (такие как число или время). С помощью DatePart () и Date () можно легко написать условие фильтрации, отбирающее заказы, сделанные в текущем месяце. DatePart("m", [DatePlaced])=DatePart("m", Date()) And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date ()) Это довольно длинное выражение на самом деле представляет собой комбинацию двух условий, соединенных ключевым словом And. Первое условие сравнивает номер месяца текущей даты с датой, хранящейся в поле DatePlaced: DatePart("m", [DatePlaced])=DatePart ("m", Date( )) Приведенное выражение устанавливает, что у обеих дат один и тот же календарный месяц, но вы должны также убедиться в том, что год у них тоже совпадает: DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date ( )) Сложность применения функции DatePart ( ) (и некоторых других функций для дат) заключается в понимании идеи компонентов, составляющих дату. Применяя символ m в функции DatePart ( ), вы получите номер месяца, а используя текст уууу, извлечете чете-рехсимвольный номер года. В табл. 7.5 приведены все возможные варианты. Таблица 7.5. Компоненты даты
Таблица 7.5 (окончание)
|