У цій статті наведено приклади виразів в Access. Вираз об'єднує математичні або логічні оператори, константи, функції, поля таблиці, елементи керування та властивості в одне значення. Вирази в Access можна використовувати, щоб обчислювати значення, перевіряти дані та встановлювати стандартні значення.
У цій статті
Форми та звіти
Запити та фільтри
Усі вирази в запитах і фільтрах
Таблиці
| Стандартні значення полів | Правила перевірки полів |
|---|
Макроси
Форми та звіти
У таблицях цього розділу відображаються вирази, які обчислюють значення в елементі керування у формі або звіті. Щоб створити обчислюваний елемент керування, введіть вираз у ControlSource властивість елемента керування, а не в полі таблиці або запиті.
Примітка.
Крім того, під час виділення даних за допомогою умовного форматування можна також використовувати вирази у формі або звіті.
Операції з текстом
Вирази в таблиці нижче використовують & оператори (амперсанд) і + (плюс), щоб об'єднувати текстові рядки, використовувати вбудовані функції для роботи з текстом або іншим чином створювати обчислюваний елемент керування.
| Вираз | Результат |
|---|---|
="N/A" |
Відображає фразу "Н/Д". |
=[FirstName] & " " & [LastName] |
Відображає значення, які містяться в полях таблиці "Ім’я" та "Прізвище". У цьому прикладі & оператор використовується для поєднання поля "Ім'я", символу пробілу (у лапках) і поля "Прізвище". |
=Left([ProductName], 1) |
Left За допомогою функції відображає перший символ значення в полі або елементі керування під назвою ProductName. |
=Right([AssetCode], 2) |
Right За допомогою функції відображає два останні символи значення в полі або елементі керування під назвою AssetCode. |
=Trim([Address]) |
Trim За допомогою функції відображає значення елемента керування після видалення пробілів Address на початку та в кінці. |
=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode]) |
IIf За допомогою функції відображає значення City елементів керування таPostalCode, якщо Region елемент керування має null-значення. В іншому разі відобразяться значення CityRegion, і PostalCode елементи керування, розділені пробілами. |
=[City] & (" " + [Region]) & " " & [PostalCode] |
+ Використовує оператор і розповсюдження null для відображення значень City і PostalCode елементів керування, якщо значення в Region полі або елементі керування має Null-значення. В іншому разі відобразяться значення полів і PostalCode елементів керування , Regionрозділених Cityпробілами. Розповсюдження Null-значення означає, що якщо будь-яка частина виразу має null-значення, увесь вираз має null-значення. Оператор + підтримує розповсюдження null, але & оператор цього не робить. |
Верхні та нижні колонтитули
Використовуйте ці Page властивості та Pages властивостях, щоб відображати або друкувати номери сторінок у формах або звітах. Ці властивості доступні лише під час друку або попереднього перегляду, тому вони не відображаються на аркуші властивостей форми або звіту. Зазвичай текстове поле розміщується в розділі верхнього або нижнього колонтитула форми або звіту, а потім використовується вираз, подібний до виразу, показаного в таблиці нижче.
Докладні відомості про використання колонтитулів у формах і звітах див. в статті Вставлення у форму або звіт номерів сторінок.
| Вираз | Результат |
|---|---|
=[Page] |
1 |
="Page " & [Page] |
Сторінка 1 |
="Page " & [Page] & " of " & [Pages] |
Сторінка 1 з 3 |
=[Page] & " of " & [Pages] & " Pages" |
1 з 3 стор. |
=[Page] & "/" & [Pages] & " Pages" |
1/3 стор. |
=[Country/region] & " - " & [Page] |
Україна – 1 |
=Format([Page], "000") |
001 |
="Printed on: " & Date() |
Дата друку: 31.12.2017 |
Арифметичні операції
Вирази дають змогу додавати, віднімати, множити й ділити значення з кількох полів або елементів керування. За допомогою виразів також можна виконувати арифметичні операції з датами. Наприклад, припустімо, у вас є поле таблиці "Дата й час" із назвою "Потрібна дата". У полі або елементі керування, прив'язаному до поля, вираз =[RequiredDate] - 2 повертає значення дати й часу, яке дорівнює двом дням до поточних значень у полі RequiredDate.
| Вираз | Результат |
|---|---|
=[Subtotal]+[Freight] |
Сума значень у полях або елементах керування "Проміжний підсумок" і "Вартість доставки". |
=[RequiredDate]-[ShippedDate] |
Інтервал між значеннями дат у полях або елементах керування "Потрібна дата" й "Дата доставки". |
=[Price]*1.06 |
Добуток значення в полі або елементі керування "Ціна" та коефіцієнта 1,06 (додає 6 відсотків до значення "Ціна"). |
=[Quantity]*[Price] |
Добуток значень у полях або елементах керування "Кількість" і "Ціна". |
=[EmployeeTotal]/[CountryRegionTotal] |
Частка значень у полях або елементах керування "Загальна кількість працівників" і "Загальна кількість країн або регіонів". |
Примітка
Якщо у виразі використовується арифметичний оператор (+, -, *або /) і один з елементів керування має null-значення, результат всього виразу дорівнює null. Це називається розповсюдженням null. Якщо елемент керування може мати null-значення, ви можете уникнути розповсюдження null за допомогою функції Nz , щоб перетворити null-значення на нуль. Наприклад, використовуйте .=Nz([Subtotal])+Nz([Freight])
Значення в інших елементах керування
Інколи може знадобитися значення, доступне в іншому місці, наприклад у полі або елементі керування в іншій формі чи в іншому звіті. Ви можете повернути це значення з іншого поля чи елемента керування за допомогою виразу.
У таблиці нижче наведено приклади виразів, які можна використовувати в обчислюваних елементах керування у формах.
| Вираз | Результат |
|---|---|
=Forms![Orders]![OrderID] |
Значення елемента керування "Ідентифікатор замовлення" у формі "Замовлення". |
=Forms![Orders]![Orders Subform].Form![OrderSubtotal] |
Значення елемента керування "Проміжний підсумок замовлень" у підформі під назвою "Підформа замовлень", розташованій у формі "Замовлення". |
=Forms![Orders]![Orders Subform]![ProductID].Column(2) |
Значення третього стовпця в багатостовпцевому списку "Ідентифікатор товару" в підформі під назвою "Підформа замовлень", розташованій у формі "Замовлення" (зверніть увагу, що 0 позначає перший стовпець, 1 – другий стовпець і т. д.). |
=Forms![Orders]![Orders Subform]![Price] * 1.06 |
Добуток значення елемента керування "Ціна" в підформі під назвою "Підформа замовлень", розташованій у формі "Замовлення", і коефіцієнта 1,06 (додає 6 відсотків до елемента керування "Ціна"). |
=Parent![OrderID] |
Значення елемента керування "Ідентифікатор замовлення" в головній або батьківській формі поточної підформи. |
Вирази в таблиці нижче демонструють деякі способи використання обчислюваних елементів керування у звітах. Вирази посилаються на властивість Report.
| Вираз | Результат |
|---|---|
=Report![Invoice]![OrderID] |
Значення елемента керування "Ідентифікатор замовлення" у звіті під назвою "Рахунок-фактура". |
=Report![Summary]![Summary Subreport]![SalesTotal] |
Значення елемента керування "Загальний обсяг продажів" у підзвіті під назвою "Зведений підзвіт" звіту "Зведення". |
=Parent![OrderID] |
Значення елемента керування "Код замовлення" в головному або батьківському звіті поточного підзвіту. |
Кількість, сума та середні значення
Щоб обчислити значення для одного або кількох полів чи елементів керування, можна скористатись агрегатною функцією. Наприклад, ви можете обчислити підсумок групи для нижнього колонтитула групи у звіті або проміжний підсумок замовлення для кожної позиції у формі. Ви також можете підрахувати кількість елементів в одному чи кількох полях або обчислити середнє значення.
Вирази в таблиці нижче демонструють деякі способи використання таких функцій, як Avg, Count і Sum.
| Вираз | Опис |
|---|---|
=Avg([Freight]) |
За допомогою функції Avg відображає середнє значення поля таблиці або елемента керування "Вартість доставки". |
=Count([OrderID]) |
За допомогою функції Count відображає кількість записів в елементі керування "Код замовлення". |
=Sum([Sales]) |
За допомогою функції Sum відображає суму значень в елементі керування "Збут". |
=Sum([Quantity]*[Price]) |
За допомогою функції Sum відображає суму добутків значень в елементах керування "Кількість" і "Ціна". |
=[Sales]/Sum([Sales])*100 |
Відображає відсоток збуту, розділяючи значення Sales елемента керування на суму всіх значень в елементі Sales керування. Якщо для властивості елемента керування Percentвстановлено Format значення , не включайте *100 його у вираз. |
Докладні відомості про використання агрегатних функцій і підсумовування значень у полях і стовпцях див. в статтях Обчислення суми даних із використанням запиту, Обчислення даних із використанням запиту, Відображення підсумків стовпців даних у табличному поданні за допомогою рядка підсумків і Відображення підсумків стовпців даних у табличному поданні.
Агрегатні функції SQL
Агрегатна функція домену, або SQL, використовується, коли потрібно обчислити суму або підрахувати кількість значень вибірково. "Домен" складається з одного чи кількох полів в одній чи кількох таблицях або з одного чи кількох елементів керування в одній чи кількох формах або звітах. Наприклад, ви можете зіставити значення в полі таблиці зі значеннями в елементі керування форми.
| Вираз | Опис |
|---|---|
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms("Suppliers")("[SupplierID]")) |
За допомогою функції DLookup повертає значення поля "Контакт" у таблиці "Постачальники", якщо значення поля "Код постачальника" в таблиці збігається зі значенням елемента керування "Код постачальника" у формі "Постачальники". |
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms![New Suppliers]![SupplierID]) |
За допомогою функції DLookup повертає значення поля "Контакт" у таблиці "Постачальники", якщо значення поля "Код постачальника" в таблиці збігається зі значенням елемента керування "Код постачальника" у формі "Нові постачальники". |
=DSum("[OrderAmount]", "[Orders]", "[CustomerID] = 'RATTC'") |
За допомогою функції DSum повертає суму значень у полі "Обсяг замовлення" в таблиці "Замовлення", якщо ідентифікатор клієнта має значення RATTC. |
=DCount("[Retired]","[Assets]","[Retired]=Yes") |
За допомогою функції DCount повертає кількість значень "Так" у полі "Списані" (поле типу "Так/Ні") таблиці "Активи". |
Операції з датами
Відстеження значень дати й часу дуже часто використовується в базах даних. Наприклад, ви можете обчислити, скільки днів минуло з дати рахунка-фактури, щоб визначити термін дебіторської заборгованості. Значення дати й часу можна відформатувати різними способами, як показано в таблиці нижче.
| Вираз | Опис |
|---|---|
=Date() |
За допомогою функції Date відображає поточну дату у вигляді mm-dd-yy, де mm місяць (від 1 до 12) dd – це день (від 1 до 31) і yy останні дві цифри року (від 1980 до 2099). |
=Format(Now(), "ww") |
За допомогою функції Format відображає номер тижня року для поточної дати, яка ww представляє тижні від 1 до 53. |
=DatePart("yyyy", [OrderDate]) |
За допомогою функції DatePart відображає чотиризначне значення року з елемента керування "Дата замовлення". |
=DateAdd("y", -10, [PromisedDate]) |
За допомогою функції DateAdd відображає дату, яка передує значенню елемента керування "Планова дата" на 10 днів. |
=DateDiff("d", [OrderDate], [ShippedDate]) |
За допомогою функції DateDiff відображає різницю днів між значеннями елементів керування "Дата замовлення" та "Дата доставки". |
=[InvoiceDate] + 30 |
За допомогою арифметичних операцій із датами обчислює дату через 30 днів після дати в полі або елементі керування "Дата рахунка". |
Умови лише з двома значеннями
У прикладах виразів із таблиці нижче використано функцію IIf, щоб повернути одне з двох можливих значень. Функція IIf передає три аргументи: Перший аргумент – це вираз, який має повернути True значення або значення False . Другий аргумент – це значення, яке повертається, якщо вираз істинний, а третій аргумент – це значення, яке повертається, якщо вираз хибний.
| Вираз | Опис |
|---|---|
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed") |
За допомогою функції IIf (Immediate If) відображає повідомлення "Замовлення підтверджено", якщо значення підтвердженого елемента керування; Yesінакше відображає повідомлення "Order Not Confirmed." |
=IIf(IsNull([Country/region]), " ", [Country]) |
За допомогою функцій IIf та IsNull відображає пустий рядок, якщо елемент керування "Країна або регіон" має Null-значення. Інакше відображає значення елемента керування "Країна або регіон". |
=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode]) |
За допомогою функцій IIf та IsNull відображає значення елементів керування "Місто" й "Поштовий індекс", якщо елемент керування "Область" має Null-значення. В іншому випадку відображає значення полів або елементів керування "Місто", "Область" і "Поштовий індекс". |
=IIf(IsNull([RequiredDate]) Or IsNull([ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate]) |
За допомогою функцій IIf та IsNull відображає повідомлення "Можливо, дату не вказано", якщо різниця потрібної дати й дати доставки є Null-значенням. Інакше відображає інтервал між значеннями дати в елементах керування "Потрібна дата" й "Дата доставки". |
Запити та фільтри
Цей розділ містить приклади виразів, за допомогою яких можна створити обчислюване поле або вказати умови в запиті. Обчислюване поле – це стовпець у запиті, що є результатом виразу. Наприклад, ви можете обчислити значення, поєднати текстові значення, як-от імена та прізвища, або відформатувати частину дати.
Щоб обмежити записи, з якими ви працюєте, додайте умову до запиту. Наприклад, за допомогою Between оператора можна вказати дату початку та завершення та обмежити результати запиту замовленнями, доставленими між цими датами.
У наведених нижче розділах наведено приклади виразів для використання в запитах.
Текстові операції в запитах
Вирази в таблиці нижче використовують & оператори та + об'єднують текстові рядки, використовують вбудовані функції для роботи з текстовим рядком або іншим чином працюють із текстом для створення обчислюваного поля.
| Вираз | Опис |
|---|---|
FullName: [FirstName] & " " & [LastName] |
Створює поле "Повне ім’я", яке відображає значення полів "Ім’я" та "Прізвище" з пробілом між ними. |
Address2: [City] & " " & [Region] & " " & [PostalCode] |
Створює поле "Адреса2", яке відображає значення полів "Місто", "Область" і "Поштовий індекс" із пробілами між ними. |
ProductInitial: Left([ProductName], 1) |
Створює поле "Перша буква товару", а потім за допомогою функції Left відображає в ньому перший символ значення в полі "Назва товару". |
TypeCode: Right([AssetCode], 2) |
Створює поле "Код типу", а потім за допомогою функції Right відображає в ньому останні два символи значень у полі "Код активу". |
AreaCode: Mid([Phone],2,3) |
Створює поле "Код міста", а потім за допомогою функції Mid відображає в ньому три символи, починаючи з другого символу значення в полі "Телефон". |
ExtendedPrice: CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100 |
Призначає обчислюваному полю ім’я "Розширена ціна" та використовує функцію CCur для обчислення загальної суми елемента рядка з урахуванням знижки. |
Арифметичні операції в запитах
Вирази дають змогу додавати, віднімати, множити й ділити значення з кількох полів або елементів керування. Арифметичні операції також можна виконувати над датами. Наприклад, припустімо, у вас є поле типу "Дата й час" із назвою "Потрібна дата". Вираз =[RequiredDate] - 2 повертає значення дати й часу, яке дорівнює за два дні до значення в полі RequiredDate.
| Вираз | Опис |
|---|---|
PrimeFreight: [Freight] * 1.1 |
Створює поле "Підвищена вартість доставки", а потім відображає в ньому вартість доставки плюс 10 відсотків. |
OrderAmount: [Quantity] * [UnitPrice] |
Створює поле "Обсяг замовлення", а потім відображає в ньому добуток значень у полях "Кількість" і "Ціна за одиницю". |
LeadTime: [RequiredDate] - [ShippedDate] |
Створює поле "Час випередження", а потім відображає в ньому різницю значень у полях "Потрібна дата" та "Дата доставки". |
TotalStock: [UnitsInStock]+[UnitsOnOrder] |
Створює поле "Загальна кількість запасів", а потім відображає в ньому суму значень у полях "Одиниць на складі" та "Одиниць замовлено". |
FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100 |
Створює поле під назвою FreightPercentage, а потім відображає відсоток вартості доставки в кожному проміжному підсумку. Цей вираз використовує Sum функцію для підсумовування значень у Freight полі, а потім ділить їх на суму значень у Subtotal полі. Щоб використати цей вираз, перетворіть вибірковий запит на запит підсумків. На бланку потрібно використати рядок Підсумок і встановити для клітинки Підсумок для цього поля значення Expression. Докладні відомості про створення запиту підсумків див. в статті Підсумування даних за допомогою запиту. Якщо для властивості поля Percentвстановлено Format значення , не додавайте *100. |
Докладні відомості про використання агрегатних функцій і підсумовування значень у полях і стовпцях див. в статтях Обчислення суми даних із використанням запиту, Обчислення даних із використанням запиту, Відображення підсумків стовпців даних у табличному поданні за допомогою рядка підсумків і Відображення підсумків стовпців даних у табличному поданні.
Операції з датами в запитах
Майже всі бази даних зберігають і відстежують дати й час. Щоб працювати з датами й часом у програмі Access, потрібно встановити для полів дати й часу в таблицях тип даних "Дата й час". В Access можна виконувати арифметичні обчислення над датами. Наприклад, ви можете обчислити, скільки днів минуло з дати рахунка-фактури, щоб визначити термін дебіторської заборгованості.
| Вираз | Опис |
|---|---|
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) |
Створює поле "Час затримки", а потім за допомогою функції DateDiff відображає в ньому кількість днів між датою замовлення та датою доставки. |
YearHired: DatePart("yyyy",[HireDate]) |
Створює поле "Рік найму", а потім за допомогою функції DatePart відображає в ньому рік, коли найнято кожного працівника. |
MinusThirty: Date( )- 30 |
Створює поле "Мінус тридцять", а потім за допомогою функції Date відображає в ньому дату, що на 30 днів передує поточній. |
Агрегатні функції SQL у запитах
Вирази в таблиці нижче використовують функції SQL, які агрегують або підсумовують дані. Часто відображаються такі функції, як Sum, Countі Avg щебінь.
Окрім агрегатних функцій, у програмі Access також передбачено агрегатні функції домену, які дають змогу вибірково підсумувати або підрахувати значення. Наприклад, ви можете порахувати значення лише в певному діапазоні або взяти значення з іншої таблиці. Агрегатні функції домену включають DSum, DCount і DAvg.
Щоб обчислити підсумки, часто потрібно створити запит підсумків. Наприклад, щоб підсумувати за групою, скористайтеся запитом підсумків. Щоб увімкнути запит підсумків із бланка запиту, виберіть Підсумки в меню Подання .
| Вираз | Опис |
|---|---|
RowCount: Count(*) |
Створює поле "Кількість рядків", а потім за допомогою функції Count рахує кількість записів у запиті, зокрема записи з пустими полями (з Null-значенням). |
FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100 |
Створює поле під назвою FreightPercentage, а потім обчислює відсоток витрат за доставку в кожному проміжному підсумкуSubtotal, розділяючи суму значень у Freight полі на суму значень у полі. У цьому прикладі використовується Sum функція. Цей вираз потрібно використовувати із запитом підсумків. Якщо для властивості поля Percentвстановлено Format значення , не додавайте *100. Докладні відомості про створення запиту підсумків див. в статті Підсумування даних за допомогою запиту. |
AverageFreight: DAvg("[Freight]", "[Orders]") |
Створює поле "Середня вартість доставки", а потім за допомогою функції DAvg обчислює середню вартість доставки для всіх замовлень, об’єднаних у запиті підсумків. |
Поля, у яких відсутні дані
Наведені тут вирази працюють із полями, у яких потенційно відсутні відомості, наприклад, які містять Null-значення (невідомі або невизначені значення). Ви часто стикаєтеся з Null-значеннями: це може бути невідома ціна нового товару або значення, яке ваші колеги забули додати до замовлення. Можливість знаходити й обробляти Null-значення може бути критично важливою частиною операцій баз даних, а вирази в наведеній нижче таблиці демонструють деякі з поширених способів обробки Null-значень.
| Вираз | Опис |
|---|---|
CurrentCountryRegion: IIf(IsNull([CountryRegion]), " ", [CountryRegion]) |
Створює поле "Поточна країна або регіон", а потім за допомогою функцій IIf та IsNull відображає пустий рядок у цьому полі, якщо поле "Країна або регіон" містить Null-значення. В іншому випадку відображає вміст поля "Країна або регіон". |
LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate]) |
Створює поле "Час випередження", а потім за допомогою функцій IIf та IsNull відображає повідомлення "Можливо, дату не вказано", якщо поле "Потрібна дата" або "Дата доставки" має Null-значення. Інакше відображає різницю дат. |
SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales]) |
Створює поле "Збут за півріччя", а потім відображає в ньому підсумок значень полів "Збут за I квартал" і "Збут за II квартал", спершу перетворивши всі Null-значення на нуль за допомогою функції Nz. |
Обчислювані поля з вкладеними запитами
Обчислюване поле також можна створити за допомогою вкладеного запиту, або підзапиту. Вираз у таблиці нижче – це один із прикладів обчислюваного поля, створеного на основі підзапиту.
| Вираз | Опис |
|---|---|
Cat: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID]=[Categories].[CategoryID]) |
Створює поле "Категорія", а потім відображає в ньому ім’я категорії, якщо поля "Код категорії" в таблицях "Категорії" та "Товари" однакові. |
Зіставлення текстових значень
Приклади виразів у цій таблиці демонструють умови, які повністю або частково відповідають текстовим значенням.
| Поле | Вираз | Опис |
|---|---|---|
| Місто доставки | "London" |
Відображає замовлення, доставлені до Києва. |
| Місто доставки | "London" Or "Hedge End" |
Or Використовує оператор для відображення замовлень, доставлених до Лондона або Хедж-енду. |
| Країна або регіон доставки | In("Canada", "UK") |
In Використовує оператор для відображення замовлень, доставлених до Канади або Великобританії. |
| Країна або регіон доставки | Not "USA" |
Not Використовує оператор для відображення замовлень, доставлених до інших країн або регіонів, окрім США. |
| Назва товару | Not Like "C*" |
Not За допомогою оператора та символу узагальнення * відображає продукти, назви яких не починаються з C. |
| Назва компанії | >="N" |
Відображає замовлення, доставлені компаніям, назви яких починаються з букв N через Z. |
| Код товару | Right([ProductCode], 2)="99" |
За допомогою функції Right відображає замовлення зі значеннями ProductCode, які закінчуються на 99. |
| Отримувач | Like "S*" |
Відображає замовлення, доставлені клієнтам, назви яких починаються з букви S. |
Зіставлення умов дат
Вирази в таблиці нижче демонструють використання дат і пов’язаних функцій у виразах умов. Докладні відомості про введення та використання значень дат див. в статті Введення значення дати або часу.
| Поле | Вираз | Опис |
|---|---|---|
| Дата доставки | #2/2/2017# |
Відображає замовлення, доставлені 2 лютого 2017 р. |
| Дата доставки | Date() |
Відображає замовлення, доставлені сьогодні. |
| Потрібна дата | Between Date( ) And DateAdd("m", 3, Date( )) |
Between...And За допомогою оператора та функцій DateAdd і Date відображає замовлення, необхідні від сьогоднішньої дати до трьох місяців від сьогоднішньої дати. |
| Дата замовлення | < Date( ) - 30 |
За допомогою функції Date відображає замовлення, зроблені понад 30 днів тому. |
| Дата замовлення | Year([OrderDate])=2017 |
За допомогою функції Year відображає замовлення, зроблені у 2017 р. |
| Дата замовлення | DatePart("q", [OrderDate])=4 |
За допомогою функції DatePart відображає замовлення за четвертий календарний квартал. |
| Дата замовлення | DateSerial(Year ([OrderDate]), Month([OrderDate])+1, 1)-1 |
За допомогою функцій DateSerial, Year та Month відображає замовлення за останній день кожного місяця. |
| Дата замовлення | Year([OrderDate])= Year(Now()) And Month([OrderDate])= Month(Now()) |
За допомогою функцій Year та Month і оператора And відображає замовлення за поточний рік і місяць. |
| Дата доставки | Between #1/5/2017# And #1/10/2017# |
Between...AndЗа допомогою оператора відображає замовлення, доставлені не раніше 5 січня 2017 року та не пізніше 10 січня 2017 року. |
| Потрібна дата | Between Date( ) And DateAdd("M", 3, Date( )) |
Between...And За допомогою оператора відображає замовлення, необхідні між сьогоднішньою датою та трьома місяцями від сьогоднішньої дати. |
| Дата народження | Month([BirthDate])=Month(Date()) |
За допомогою функцій Month і Date відображає працівників, дні народження яких припадають на цей місяць. |
Пошук відсутніх даних
Вирази в таблиці нижче мають справу з полями, які містять потенційно відсутні дані, тобто полями, які можуть містити Null-значення або рядок нульової довжини. Null-значення позначає відсутність інформації. Це не нуль і ніяке інше значення. Програма Access підтримує поняття відсутньої інформації, тому що це важливо для цілісності бази даних. У реальному світі ми часто чогось не знаємо, навіть якщо це лише тимчасово (наприклад, поки що не визначену ціну на новий товар). Таким чином, у базі даних, яка моделює реальну сутність, як-от компанію, має бути змога записувати дані як відсутні. Щоб дізнатися, чи поле або елемент керування містить Null-значення, можна скористатися функцією IsNull, а щоб перетворити Null-значення на нуль – функцією Nz.
| Поле | Вираз | Опис |
|---|---|---|
| Регіон доставки | Is Null |
Відображає замовлення для клієнтів, для яких поле "Регіон доставки" має Null-значення (пусте). |
| Регіон доставки | Is Not Null |
Відображає замовлення для клієнтів, для яких поле "Регіон доставки" має якесь значення. |
| Факс | "" |
Відображає замовлення для клієнтів, які не мають факсимільного пристрою, що позначено значенням рядка нульової довжини в полі "Факс", а не Null-значенням (відсутнім значенням). |
Зіставлення шаблонів записів за допомогою оператора Like
Оператор Like забезпечує велику гнучкість, коли ви намагаєтеся зіставити рядки, які відповідають шаблону, оскільки ви можете використовувати Like їх із символами узагальнення та визначити шаблони, які мають відповідати access. Наприклад, * символ узагальнення (зірочка) відповідає послідовності символів будь-якого типу та дає змогу легко знайти всі імена, які починаються з букви. Наприклад, за допомогою виразу Like "S*" можна знайти всі імена, які починаються з букви S. Докладні відомості див. в статті Оператор Like.
| Поле | Вираз | Опис |
|---|---|---|
| Одержувач | Like "S*" |
Знаходить усі записи в полі "Отримувач", які починаються з букви С. |
| Отримувач | Like "*Imports" |
Знаходить усі записи в полі "Отримувач", які закінчуються словом "імпорт". |
| Одержувач | Like "[A-D]*" |
Знаходить усі записи в полі "Одержувач", які починаються з букви А, Б, В або Г. |
| Одержувач | Like "*ar*" |
Знаходить усі записи в полі "Одержувач", які містять буквосполучення "но". |
| Отримувач | Like "Maison Dewe?" |
Знаходить усі записи в полі "Отримувач", які містять слово "Богдан", після якого йде рядок із п’яти букв, перші чотири з яких – це "Козя", а остання буква не відома. |
| Отримувач | Not Like "A*" |
Знаходить усі записи в полі "Одержувач", які не починаються з букви А. |
Зіставлення рядків за допомогою агрегатних функцій SQL
Доменна агрегатна функція використовується, коли потрібно обчислити суму, підрахувати кількість або знайти середнє значення вибірково. Наприклад, ви можете порахувати лише ті значення, які містяться в певному діапазоні або дорівнюють "Так". В інших випадках може знадобитися шукати значення з іншої таблиці, щоб відобразити його. Приклади виразів у таблиці нижче за допомогою доменних агрегатних функцій обчислюють набір значень, щоб використати результат як умову запиту.
| Поле | Вираз | Опис |
|---|---|---|
| Вартість доставки | > (DStDev("[Freight]", "Orders") + DAvg("[Freight]", "Orders")) |
За допомогою функцій DStDev і DAvg відображає всі замовлення, для яких вартість доставки перевищує суму середнього значення та стандартного відхилення для вартості доставки. |
| Кількість | > DAvg("[Quantity]", "[Order Details]") |
За допомогою функції DAvg відображає товари, замовлені в кількості, що перевищує середню кількість замовлення. |
Зіставлення полів за допомогою вкладених запитів
Значення для умови можна обчислити за допомогою підзапиту, або вкладеного запиту. Приклади виразів у таблиці нижче підбирають рядки на основі результатів, які повертає підзапит.
| Поле | Вираз | Відображення |
|---|---|---|
| Ціна за одиницю | (SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "Aniseed Syrup") |
Товари з такою ж ціною, що й анісовий сироп. |
| Ціна за одиницю | >(SELECT AVG([UnitPrice]) FROM [Products]) |
Товари, ціна за одиницю яких вища середньої. |
| Оклад | > ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*Manager*") OR ([Title] LIKE "*Vice President*")) |
Оклад кожного торгового представника, чий оклад перевищує оклад усіх працівників зі словом "Керівник" або "Віце-президент" у посаді. |
| Вартість замовлення: [Ціна за одиницю] * [Кількість] | > (SELECT AVG([UnitPrice] * [Quantity]) FROM [Order Details]) |
Замовлення, вартість яких перевищує середнє значення замовлення. |
Оновлення запитів
Скористайтеся запитом на оновлення, щоб змінити дані в одному або кількох наявних полях бази даних. Наприклад, ви можете замінити значення або повністю видалити їх. У цій таблиці наведено кілька способів використання виразів у запитах на оновлення. Використовуйте ці вирази в Update To рядку бланка запиту для поля, яке потрібно оновити.
Докладні відомості про створення запитів на оновлення див. в статті Створення й виконання запиту на оновлення.
| Поле | Вираз | Результат |
|---|---|---|
| Заголовок | "Salesperson" |
Змінює текстове значення на "Торговий представник". |
| Початок проекту | #8/10/17# |
Змінює значення дати на 10 серпня 2017 р. |
| Закрито | Yes |
У полі типу "Так/Ні" змінює значення "Ні" на "Так". |
| Номер партії | "PN" & [PartNumber] |
Додає "НП" до початку номера кожної вказаної партії. |
| Підсумок для позиції | [UnitPrice] * [Quantity] |
Множить ціну за одиницю товару на кількість. |
| Вартість доставки | [Freight] * 1.5 |
Збільшує вартість доставки на 50 відсотків. |
| Збут | DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID]) |
Якщо значення "Код товару" в поточній таблиці відповідають значенням "Код товару" в таблиці "Відомості про замовлення", оновлює загальний обсяг продажів на основі добутку кількості товару та ціни за одиницю. |
| Поштовий індекс доставки | Right([ShipPostalCode], 5) |
Видаляє крайні ліві символи, залишаючи п’ять символів праворуч. |
| Ціна за одиницю | Nz([UnitPrice]) |
Замінює Null-значення (невизначене або невідоме) у полі "Ціна за одиницю" на нуль (0). |
Інструкції SQL
мова структурованих запитів (SQL) – це мова запитів, яку використовує Access. Кожен запит, створений у режимі конструктора запитів, також можна виразити за допомогою sql. Щоб переглянути інструкцію SQL для будь-якого запиту, виберіть пункт РЕЖИМ SQL у меню Подання . У таблиці нижче наведено зразки інструкцій SQL, які використовують вираз.
| Інструкція SQL із виразом | Результат |
|---|---|
SELECT [FirstName],[LastName] FROM [Employees] WHERE [LastName]="Danseglio"; |
Відображає значення в полях "Ім’я" та "Прізвище" для працівників із прізвищем "Герасименко". |
SELECT [ProductID],[ProductName] FROM [Products] WHERE [CategoryID]=Forms![New Products]![CategoryID]; |
Відображає значення в полях "Ідентифікатор товару" та "Назва товару" в таблиці "Товари" для записів, у яких значення "Ідентифікатор категорії" відповідає значенню "Ідентифікатор категорії" з відкритої форми "Нові товари". |
SELECT Avg([ExtendedPrice]) AS [Average Extended Price] FROM [Order Details Extended] WHERE [ExtendedPrice]>1000; |
Обчислює середню розширену ціну для замовлень, у яких значення поля "Розширена ціна" перевищує 1000, а потім відображає її в полі під назвою "Середня розширена ціна". |
SELECT [CategoryID], Count([ProductID]) AS [CountOfProductID] FROM [Products] GROUP BY [CategoryID] HAVING Count([ProductID])>10; |
У полі "Кількість кодів товару" відображається загальна кількість товарів для категорій, у яких понад 10 товарів. |
Вирази таблиці
Два найпоширеніші способи використання виразів у таблиці – призначення стандартного значення та створення правила перевірки.
Стандартні значення полів
Створюючи базу даних, можна призначити стандартне значення для поля або елемента керування. Після цього Програма Access надає це значення, коли створюється новий запис, який містить поле, або коли створюється об'єкт, який містить елемент керування. Вирази в таблиці нижче відображають зразки значень за замовчуванням для поля або елемента керування. Якщо елемент керування прив'язано до поля в таблиці, а поле має стандартне значення, стандартне значення елемента керування має вищий пріоритет.
| Поле | Вираз | Стандартне значення поля |
|---|---|---|
| Quantity | 1 |
1 |
| Регіон | "MT" |
Закарпаття |
| Регіон | "New York, N.Y." |
Сумська обл. (Зверніть увагу: значення з пунктуаційними знаками потрібно брати в лапки). |
| Факс | "" |
Рядок нульової довжини вказує, що за замовчуванням це поле має бути пустим, а не містити Null-значення |
| Дата замовлення | Date( ) |
Поточна дата |
| Термін | Date() + 60 |
Дата через 60 днів після сьогоднішньої |
Правила перевірки полів
За допомогою виразу можна створити правило перевірки для поля або елемента керування. Тоді програма Access застосовуватиме це правило, коли в поле або елемент керування вводитимуться дані. Щоб створити правило перевірки, змініть ValidationRule властивість поля або елемента керування. Слід також настроїти властивість ValidationText , яка містить текст, який відображається в програмі Access у разі порушення правила перевірки. Якщо не задати властивість ValidationText , в Access відобразиться повідомлення про помилку за замовчуванням.
У наведеній нижче таблиці наведено вирази правил перевірки для ValidationRule властивості та пов'язаний текст властивості ValidationText .
| Властивість ValidationRule | Властивість ValidationText |
|---|---|
<> 0 |
Введіть ненульове значення. |
0 Or > 100 |
Значення має дорівнювати 0 або бути більше за 100. |
Like "K???" |
Значення має складатися з чотирьох символів і починатися з букви "К". |
< #1/1/2017# |
Введіть дату до 01.01.2017. |
>= #1/1/2017# And < #1/1/2008# |
Дата має припадати на 2017 рік. |
Докладні відомості про перевірку даних див. в статті Створення правила перевірки для перевірки даних у полі.
Вирази макросу
Інколи потрібно виконати дію або послідовність дій макросу, лише якщо певна умова істинна. Наприклад, припустімо, що потрібно запускати дію, лише якщо значення текстового поля "Лічильник" становить 10. Вираз використовується для визначення умови в блоці Якщо:
[Counter]=10
Як і у випадку з властивістю ValidationRule , вираз у блоці Якщо – це умовний вираз. Її має бути встановлено на True або False. Дія відбувається, лише коли умова повертає значення True.
| Вираз для виконання дії | If |
|---|---|
[City]="Paris" |
Одеса – це значення міста в полі форми, з якої запущено макрос. |
DCount("[OrderID]", "Orders") > 35 |
У полі "Код замовлення" таблиці "Замовлення" є понад 35 записів. |
DCount("*", "[Order Details]", "[OrderID]=" & Forms![Orders]![OrderID]) > 3 |
У таблиці "Відомості про замовлення" є понад трьох записів, для яких поле "Код замовлення" таблиці відповідає полю "Код замовлення" у формі "Замовлення". |
[ShippedDate] Between #2-Feb-2017# And #2-Mar-2017# |
Значення поля "Дата доставки" у формі, з якої запущено макрос, припадає на період від 2 лютого 2017 р. до 2 березня 2017 р. |
Forms![Products]![UnitsInStock] < 5 |
Значення поля "Одиниць на складі" у формі "Товари" менше за 5. |
IsNull([FirstName]) |
Поле "Ім’я" у формі, з якої запущено макрос, має Null-значення (значення відсутнє). Цей вираз еквівалентний виразу: [Ім’я] Is Null. |
[CountryRegion]="UK" And Forms![SalesTotals]![TotalOrds] > 100 |
Поле "Країна або регіон" у формі, з якої запущено макрос, має значення "Україна", а значення поля "Усього замовлень" у формі "Загальний обсяг збуту" перевищує 100. |
[CountryRegion] In ("France", "Italy", "Spain") And Len([PostalCode])<>5 |
Поле "Країна або регіон" у формі, з якої запущено макрос, має значення "Франція", "Італія" або "Іспанія", а поштовий індекс не складається з 5 символів. |
MsgBox("Confirm changes?",1)=1 |
У діалоговому вікні, яке відобразить функція MsgBox, натисніть кнопку OK. Якщо в цьому діалоговому вікні натиснути кнопку Скасувати, Access пропустить дію. |