У цій статті наведено приклади виразів у програмі 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-значення. В іншому разі значення поля CityRegionPostalCode або елементів керування відображаються через пробіл, а також полів або елементів керування. Розповсюдження Null-значення означає, що якщо будь-яка частина виразу має Null-значення, то увесь вираз отримує Null-значення. Оператор + підтримує розповсюдження Null, а & оператор – ні. |
Верхні та нижні колонтитули
Використовуйте властивості і PagePages для відображення та друку номерів сторінок у формах або звітах. Ці властивості доступні лише під час друку або попереднього перегляду, тому вони не відображаються у вікні властивостей форми або звіту. Зазвичай потрібно розмістити текстове поле в розділі верхнього або нижнього колонтитула форми або звіту, а потім використати вираз, подібний до наведених у таблиці нижче.
Докладні відомості про використання колонтитулів у формах і звітах див. в статті Вставлення у форму або звіт номерів сторінок.
| Вираз | Результат |
|---|---|
=[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 , яка перетворює це значення на нуль. Наприклад, використовуйте =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 керування. Якщо ви встановили Format для властивості елемента керування Percentзначення , не включайте *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 передає три аргументи: Перший аргумент – це вираз, який має повернути значення or False (абоTrue). Другий аргумент – це значення, яке повертається, якщо вираз істинний, а третій аргумент – це значення, яке повертається, якщо вираз хибний.
| Вираз | Опис |
|---|---|
=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 можна вказати дату початку й завершення та обмежити результати запиту замовленнями, доставленими між цими датами.
У розділах нижче наведено приклади виразів, які можна використовувати в запитах.
Операції з текстом у запитах
У виразах у наведеній нижче таблиці оператори AND + використовуються & для поєднання текстових рядків, використання вбудованих функцій для операцій із текстовими рядками або інших операцій із текстом для створення обчислюваних полів.
| Вираз | Опис |
|---|---|
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 повертає значення дати й часу, рівне дводенному значенню в полі "Потрібна дата".
| Вираз | Опис |
|---|---|
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. Докладні відомості про створення запиту підсумків див. в статті "Підсумування даних за допомогою запиту". Якщо ви задали Format властивості поля , Percentне включайте *100. |
Докладні відомості про використання агрегатних функцій і підсумовування значень у полях і стовпцях див. в статтях Обчислення суми даних із використанням запиту, Обчислення даних із використанням запиту, Відображення підсумків стовпців даних у табличному поданні за допомогою рядка підсумків і Відображення підсумків стовпців даних у табличному поданні.
Операції з датами в запитах
Майже всі бази даних зберігають і відстежують дати й час. Щоб працювати з датами й часом у програмі Access, потрібно встановити для полів дати й часу в таблицях тип даних "Дата й час". В Access можна виконувати арифметичні обчислення над датами. Наприклад, ви можете обчислити, скільки днів минуло з дати рахунка-фактури, щоб визначити термін дебіторської заборгованості.
| Вираз | Опис |
|---|---|
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) |
Створює поле "Час затримки", а потім за допомогою функції DateDiff відображає в ньому кількість днів між датою замовлення та датою доставки. |
YearHired: DatePart("yyyy",[HireDate]) |
Створює поле "Рік найму", а потім за допомогою функції DatePart відображає в ньому рік, коли найнято кожного працівника. |
MinusThirty: Date( )- 30 |
Створює поле "Мінус тридцять", а потім за допомогою функції Date відображає в ньому дату, що на 30 днів передує поточній. |
Агрегатні функції SQL у запитах
У виразах у таблиці нижче використовуються функції SQL, які об'єднують або підсумовують дані. Ви часто зустрічаєте такі функції, як Sum, CountAvg , і які називаються агрегатними функціями.
Окрім агрегатних функцій, у програмі Access також передбачено агрегатні функції домену, які дають змогу вибірково підсумовувати або обчислювати значення. Наприклад, ви можете порахувати значення лише в певному діапазоні або взяти значення з іншої таблиці. Агрегатні функції домену включають DSum, DCount і DAvg.
Щоб обчислити підсумки, часто потрібно створити запит підсумків. Наприклад, щоб підсумувати значення групи, скористайтеся запитом підсумків. Щоб увімкнути запит підсумків у сітці макета запиту, у меню "Подання" виберіть пункт "Підсумки".
| Вираз | Опис |
|---|---|
RowCount: Count(*) |
Створює поле "Кількість рядків", а потім за допомогою функції Count рахує кількість записів у запиті, зокрема записи з пустими полями (з Null-значенням). |
FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100 |
Створює поле з іменем FreightPercentage, а потім обчислює відсоток транспортних витрат у кожному проміжному підсумку діленням суми значень у Freight полі на суму значень у полі.Subtotal У цьому прикладі використовується функція Sum . Цей вираз потрібно використовувати із запитом підсумків. Якщо ви задали Format властивості поля , Percentне включайте *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 оператор і * символ узагальнення для відображення продуктів, назви яких не починаються з букви С. |
| Назва компанії | >="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-jan-2017 і не пізніше 10-jan-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*" можна знайти всі імена, які починаються з букви "С". Докладні відомості див. в статті Оператор "Подобається".
| Поле | Вираз | Опис |
|---|---|---|
| Одержувач | 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 властивість, вираз у блоці If є умовним виразом. Він має бути вирішений на одну або TrueFalse. Дія відбувається, лише коли умова повертає значення 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 пропустить дію. |