運算式的範例

本文提供多個 Access 運算式的範例。運算式是數學或邏輯運算子、常數、函數、資料表欄位、控制項和屬性的組合,用來估算出單一數值。您可以在 Access 中使用運算式來計算數值、驗證資料及設定預設值。

本文內容

表單和報表

本節中的表格提供運算式如何計算表單或報表中控制項之值的範例。若要建立計算控制項,請在控制項的 ControlSource 屬性 (而非資料表欄位或查詢) 中輸入運算式。

注意   :您也可以表單或報表中使用運算式,以使用設定格式化的條件來醒目提示資料

文字運算

下表中的運算式使用了 &+ (加號) 運算子來合併文字字串和內建函數,以操縱文字字串,或是按文字運作以建立計算控制項。

運算式

結果

="N/A"

會顯示 N/A。

=[FirstName] & " " & [LastName]

會顯示位於 FirstName 和 LastName 資料表欄位內的值。在此範例中,& 運算子用來合併 FirstName 欄位、空白字元 (以引號括住) 及 LastName 欄位。

=Left([ProductName], 1)

使用 Left 函數顯示 ProductName 欄位或控制項值的第一個字元。

=Right([AssetCode], 2)

使用 Right 函數顯示 AssetCode 欄位或控制項值的最後 2 個字元。

=Trim([Address])

使用 Trim 函數顯示移除任何前置或後置空格的 Address 控制項值。

=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])

如果 Region 控制項中的值為 Null,使用 IIf 函數顯示 City 和 PostalCode 控制項的值;否則,則顯示 City、Region 和 PostalCode 控制項的值 (以空格分隔)。

=[City] & (" " + [Region]) & " " & [PostalCode]

如果 Region 欄位或控制項中的值為 Null,使用 + 運算子和 Null 傳播顯示 City 和 PostalCode 控制項的值;否則,則顯示 City、Region 和 PostalCode 欄位或控制項的值 (以空格分隔)。

Null 傳播表示運算式中如有任何元件為 Null,則整個運算式也為 Null。+ 運算子支援 Null 傳播;& 運算子則否。

頁面頂端

頁首及頁尾

您可以使用 PagePages 屬性顯示或列印表單或報表中的頁碼。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()

列印日期:12/31/17

頁面頂端

算術運算

您可以利用運算式將兩個以上的欄位或控制項中的值相加、相減、相乘和相除。您也可以使用運算式來執行日期的算術運算。舉例來說,假設您有個日期/時間資料表的欄位,稱為 [RequiredDate]。在與該欄位相連的欄位或控制項中,運算式 =[RequiredDate] - 2 傳回的日期/時間值等於 [RequiredDate] 欄位中目前值的 2 天前。

運算式

結果

=[Subtotal]+[Freight]

Subtotal 和 Freight 欄位或控制項值的總和。

=[RequiredDate]-[ShippedDate]

RequireDate 和 ShippedDate 欄位或控制項兩者日期值之間相隔的天數。

=[Price]*1.06

Price 欄位或控制項值乘以 1.06 (也就是另加上 Price 值的百分之 6) 的結果。

=[Quantity]*[Price]

Qantity 和 Price 欄位或控制項值相乘的結果。

=[EmployeeTotal]/[CountryRegionTotal]

EmployeeTotal 欄位或控制項值除以 CountryRegionTotal 欄位或控制項值的結果。

注意   :在運算式中使用算術運算子 (+-*/) 時,若運算式內其中一個控制項的值為 Null,則整個運算式的結果即為 Null — 此原則稱為 Null 傳播。如果您用於運算式的某個控制項記錄可能含有 Null 值,可使用 Nz 函數將 Null 值轉換為零,藉此避免 Null 傳播情形發生 — 例如 =Nz([Subtotal])+Nz([Freight])

頁面頂端

其他控制項中的值

有時候,您會需要存在其他資源中的值,例如存在另一份表單或報表的欄位或控制項中的值。這時您可以使用運算式來傳回其他欄位或控制項的值。

下表列出您可用於表單上計算控制項的運算式範例。

運算式

結果

=Forms![Orders]![OrderID]

在 Orders 表單中,OrderID 控制項的值。

=Forms![Orders]![Orders Subform].Form![OrderSubtotal]

在 Orders 表單中,名為 Orders Subform 之子表單上的 OrderSubtotal 控制項值。

=Forms![Orders]![Orders Subform]![ProductID].Column(2)

在 Orders 表單中,子表單名為 Orders Subform 上的多欄清單方塊 ProductID,其第三欄的值 (請注意,0 代表第一欄,1 代表第二欄,以此類推)。

=Forms![Orders]![Orders Subform]![Price] * 1.06

在 Orders 表單中,子表單名為 Orders Subform 上的 Price 控制項值乘以 1.06 (也就是另加上 Price 控制項值的百分之 6) 的結果。

=Parent![OrderID]

目前子表單的主要或上層表單中,OrderID 控制項的值。

下表的運算式顯示報表中使用計算控制項的幾種方式。運算式會參照 Report 屬性

運算式

結果

=Report![Invoice]![OrderID]

名為 "Invoice" 的報表中 "OrderID" 控制項的值。

=Report![Summary]![Summary Subreport]![SalesTotal]

Summary 報表中,子報表名為 Summary Subreport 上的 SalesTotal 控制項的值。

=Parent![OrderID]

在目前子報表的主要或父報表中,OrderID 控制項的值。

頁面頂端

計數、加總和平均值

您可以利用稱為彙總函數的這類函數來計算一或多個欄位或控制項的值。例如,您可以計算報表中群組尾的群組總數,或是表單上明細項目的訂單小計。您也可以計算一或多個欄位中的項目數目,或是計算平均值。

下表中的運算式說明 Avg、Count 和 Sum 等函數的幾種使用方式。

運算式

描述

=Avg([Freight])

使用 Avg 函數顯示名為 "Freight" 的資料表欄位或控制項值的平均值。

=Count([OrderID])

使用 Count 函數顯示 OrderID 控制項中記錄的數目。

=Sum([Sales])

使用 Sum 函數顯示 Sales 控制項值的加總。

=Sum([Quantity]*[Price])

使用 Sum 函數顯示 Quantity 和 Price 控制項值相乘的加總。

=[Sales]/Sum([Sales])*100

顯示銷售額所佔的百分比,由 Sales 控制項值除以 Sales 控制項所有值的加總。

如果您將控制項的 Format 屬性設為 Percent,則請勿將 *100 納入運算式中。

如需關於使用彙總函數和加總欄位和資料行中之值的詳細資訊,請參閱使用查詢加總資料使用查詢計算資料計算資料工作表中的資料列數以及顯示資料工作表中的資料行總數這幾篇文章。

頁面頂端

SQL 彙總函數

若需選擇性加總或計算值,您可以使用 SQL 或範圍彙總函數這類函數。「範圍」包括一或多個資料表中的一或多個欄位,或是一或多個表單或報表中的一或多個控制項。例如,您可以藉此比對資料表欄位值和表單控制項值。

運算式

描述

=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms("Suppliers")("[SupplierID]"))

使用 DLookup 函數,在 Suppliers 資料表中的 SupplierID 欄位值與 Suppliers 表單中的 SupplierID 控制項值相符時,傳回資料表中的 ContactName 欄位值。

=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms![New Suppliers]![SupplierID])

使用 DLookup 函數,在 Suppliers 資料表中的 SupplierID 欄位值與 New Suppliers 表單中的 SupplierID 控制項值相符時,傳回資料表中的 ContactName 欄位值。

=DSum("[OrderAmount]", "[Orders]", "[CustomerID] = 'RATTC'")

使用 DSum 函數,在 CustomerID 為 Rattc 時傳回在 Orders 資料表中 OrderAmount 欄位值的總計。

=DCount("[Retired]","[Assets]","[Retired]=Yes")

使用 DCount 函數傳回 Assets 資料表中 Retired 欄位 (Yes/No 欄位) 為 Yes 值的數目。

頁面頂端

日期運算

追蹤日期和時間是基本的資料庫活動。例如,您可計算目前距離發票日期已過幾天,藉此追蹤應收帳款的時間。如下表所示,有多種日期和時間格式可供您採用。

運算式

描述

=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 函數顯示 OrderDate 控制項值的四位數西元年份。

=DateAdd("y", -10, [PromisedDate])

使用 DateAdd 函數顯示 PromisedDate 控制項值 10 天前的日期。

=DateDiff("d", [OrderDate], [ShippedDate])

使用 DateDiff 函數顯示 OrderDate 和 ShippedDate 控制項值之間相隔的天數。

=[InvoiceDate] + 30

使用日期的算術運算來計算 [InvoiceDate] 欄位或控制項中日期過後的第 30 天。

頁面頂端

僅有兩個值的條件

下表中的範例運算式使用 IIf 函數來傳回可能的值 (二擇一)。您傳遞三個引數給 IIf 函數:第一個引數是必須傳回 TrueFalse 值的運算式;第二個引數是運算式若為 True 要傳回的值;第三個引數則是運算式若為 False 要傳回的值。

運算式

描述

=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")

使用 IIf (Immediate If) 函數,在 Confirmed 控制項的值為 Yes 時,顯示「訂單已確認」訊息;否則,則顯示「"Order Not Confirmed."」訊息。

=IIf(IsNull([Country/region]), " ", [Country])

使用 IIf 和 IsNull 函數,在 Country/region 控制項值為 Null 時顯示空字串;否則,則顯示 Country/region 控制項的值。

=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])

使用 IIf 和 IsNull 函數,在 Region 控制項值為 Null 時顯示 City 和 PostalCode 控制項的值;否則,則顯示 City、Region 和 PostalCode 欄位或控制項的值。

=IIf(IsNull([RequiredDate]) Or IsNull([ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])

使用 IIf 和 IsNull 函數,在 RequiredDate 扣除 ShippedDate 的結果為 Null 時顯示「檢查是否缺少日期」訊息;否則,則顯示 RequiredDate 和 ShippedDate 兩個控制項日期值之間相隔的時間。

頁面頂端

查詢和篩選

本節提供您可用來在查詢中建立導出欄位或為查詢提供準則的運算式範例。導出欄位為查詢中的資料行,其內容為運算式結果。舉例來說,您可以計算值、合併文字值 (例如姓氏與名字),或設定日期部分的格式。

您可以在查詢中使用準則來限制您處理的記錄。例如,您可以使用 Between 運算子來提供開始和結束日期,將查詢結果限制在這兩個日期之間所出貨的訂單。

以下提供查詢中所用運算式的範例。

文字運算

下表中的運算式使用 &+ 運算子來合併文字字串、使用內建函數來按文字字串執行,或是按文字執行來建立導出欄位。

運算式

描述

FullName: [FirstName] & " " & [LastName]

建立名為 FullName 的欄位來顯示 FirstName 和 LastName 欄位中的值 (以空格分隔)。

Address2: [City] & " " & [Region] & " " & [PostalCode]

建立名為 Address2 的欄位來顯示 City、Region 和 PostalCode 欄位中的值 (以空格分隔)。

ProductInitial: Left([ProductName], 1)

建立名為 ProductInitial 的欄位,然後使用 Left 函數在 ProductInitial 欄位中顯示 ProductName 欄位值的第一個字元。

TypeCode: Right([AssetCode], 2)

建立名為 TypeCode 的欄位,然後使用 Right 函數顯示 AssetCode 欄位值的最後兩個字元。

AreaCode: Mid([Phone],2,3)

建立名為 AreaCode 的欄位,然後使用 Mid 函數顯示 Phone 欄位值第二個字元開始的三個字元。

ExtendedPrice: CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100

將導出欄位命名為 ExtendedPrice,並使用 CCur 函數來運計算套用了折扣後的明細項目加總。

頁面頂端

算術運算

您可以利用運算式將兩個以上的欄位或控制項中的值相加、相減、相乘和相除。您也可以執行日期的算術運算。舉例來說,假設您有個日期/時間欄位,稱為 [RequiredDate]。運算式 =[RequiredDate] - 2 傳回的日期/時間值等於 [RequiredDate] 欄位中目前值的 2 天前。

運算式

描述

PrimeFreight: [Freight] * 1.1

建立名為 PrimeFreight 的欄位,然後在欄位中顯示另加了百分之 10 的運費。

OrderAmount: [Quantity] * [UnitPrice]

建立名為 OrderAmount 欄位,然後顯示 Quantity 和 UnitPrice 欄位值相乘的結果。

LeadTime: [RequiredDate] - [ShippedDate]

建立名為 LeadTime 的欄位,然後顯示 RequiredDate 和 ShippedDate 欄位值相差的時間。

TotalStock: [UnitsInStock]+[UnitsOnOrder]

建立名為 TotalStock 的欄位,然後顯示 UnitsInStock 和 UnitsOnOrder 欄位值的總和。

FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100

建立名為 FreightPercentage 的欄位,然後顯示運費在每個小計中所佔的百分比。此運算式會使用 Sum 函數來加總 Freight 欄位中的值,再將加總結果除以 Subtotal 欄位值的總和。

使用這個運算式會使用到設計格線中的 [合計] 資料列,因此您必須將所選查詢轉換成合計查詢,同時也必須為這個欄位將 [合計] 儲存格設為運算式

如需有關如何建立合計查詢的詳細資訊,請參閱使用查詢加總資料一文。

如果您將欄位的 Format 屬性設為 Percent,則請勿將 *100 納入運算式中。

如需關於使用彙總函數和加總欄位和資料行中之值的詳細資訊,請參閱使用查詢加總資料使用查詢計算資料計算資料工作表中的資料列數以及顯示資料工作表中的資料行總數這幾篇文章。

頁面頂端

日期運算

幾乎所有的資料庫都可以儲存及追蹤日期和時間。將資料表中的日期和時間欄位設為日期/時間資料類型,即可在 Access 中處理日期和時間。Access 可以執行日期的算術計算;例如,您可計算目前距離發票日期已過幾天,藉此追蹤應收帳款的時間。

運算式

描述

LagTime: DateDiff("d", [OrderDate], [ShippedDate])

建立名為 LagTime 的欄位,然後使用 DateDiff 函數顯示訂單日期和出貨日期相隔的天數。

YearHired: DatePart("yyyy",[HireDate])

建立名為 YearHired 的欄位,然後使用 DatePart 函數顯示每位員工的到職年份。

MinusThirty: Date( )- 30

建立名為 MinusThirty 的欄位,然後使用 Date 函數顯示目前日期 30 天前的日期。

頁面頂端

SQL 彙總函數

下表中的運算式使用了 SQL (結構化查詢語言) 函數來彙總或合計資料。這類函數通常稱為彙總函數 (例如 Sum、Count 和 Avg)。

除了彙總函數,Access 也提供「範圍」彙總函數供您用來選擇性加總或計算值。例如,您可以只計算特定範圍內的值,或查詢其他資料表的值。範圍彙總函數集包含 DSum FunctionDCount FunctionDAvg Function

若要計算總計,您通常需要建立合計查詢。例如,若要依群組合計,您必須使用合計查詢。若要從查詢設計格線啟用合計查詢,請按一下 [檢視] 功能表上的 [合計]。

運算式

描述

RowCount: Count(*)

建立名為 RowCount 的欄位,然後使用 Count 函數計算查詢中的記錄數目,包含具有 Null (空白) 欄位的記錄在內。

FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100

建立名為 FreightPercentage 的欄位,然後將 Freight 欄位值的總計除以 Subtotal 欄位值的總計,以計算運費在每個小計中所佔的百分比 (此範例使用 Sum 函數)。

您必須搭配合計查詢來使用這個運算式。如果您將欄位的 Format 屬性設為 Percent,則請勿將 *100 納入運算式中。

如需有關如何建立合計查詢的詳細資訊,請參閱使用查詢加總資料一文。

AverageFreight: DAvg("[Freight]", "[Orders]")

建立名為 AverageFreight 的欄位,然後使用 DAvg 函數來計算合併於合計查詢中所有訂單的平均運費。

頁面頂端

包含遺失資料的欄位

此處顯示的運算式處理可能遺漏資訊的欄位,例如包含 Null (未知或未定義) 值的欄位。您常會碰到 Null 值,像是新產品價格未知,或是同事忘記在訂單中加入值時,就會遇到這種情況。找出並處理 Null 值是資料庫運作的重要環節,下表中的運算式將示範處理 Null 值的幾種常見方式。

運算式

描述

CurrentCountryRegion: IIf(IsNull([CountryRegion]), " ", [CountryRegion])

建立名為 CurrentCountryRegion 的欄位,然後使用 IIf 和 IsNull 函數,在 CountryRegion 欄位包含 Null 值時,於該欄位中顯示空字串;否則,則顯示 CountryRegion 欄位的內容。

LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])

建立名為 LeadTime 的欄位,然後使用 IIf 和 IsNull 函數,在 RequiredDate 欄位或 ShippedDate 欄位的值為 Null 時,顯示「檢查是否缺少日期」訊息;否則,則顯示相差的日期。

SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales])

建立名為 SixMonthSales 的欄位,先使用 Nz 函數將所有 Null 值轉換為零,然後再顯示 Qtr1Sales 和 Qtr2Sales 欄位值的總計。

頁面頂端

使用子查詢來導出欄位

您可以使用巢狀查詢 (也稱為子查詢) 來建立導出欄位。下表中的運算式示範從子查詢結果取得的導出欄位。

運算式

描述

Cat: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID]=[Categories].[CategoryID])

建立名為 Cat 的欄位,在 Categories 資料表中的 CategoryID 與 Products 資料表中的 CategoryID 相同時顯示 CategoryName。

頁面頂端

比對文字值

本表中的範例運算式示範比對全部或部分文字值的準則。

欄位

運算式

描述

ShipCity

"London"

顯示出貨至倫敦的訂單。

ShipCity

"London" Or "Hedge End"

使用 Or 運算子顯示出貨至倫敦或赫奇恩德的訂單。

ShipCountryRegion

In("Canada", "UK")

使用 In 運算子顯示出貨至加拿大或英國的訂單。

ShipCountryRegion

Not "USA"

使用 Not 運算子顯示出貨至美國以外之國家/地區的訂單。

ProductName

Not Like "C*"

使用 Not 運算子和 * 萬用字元來顯示名稱開頭不是 C 的產品。

CompanyName

>="N"

顯示名稱的字母開頭介於 NZ 的訂單收件公司。

ProductCode

Right([ProductCode], 2)="99"

使用 Right 函數顯示 ProductCode 值結尾為 99 的訂單。

ShipName

Like "S*"

顯示名稱的字母開頭為 S 的訂單收件客戶。

頁面頂端

比較資料準則

下表中的運算式示範如何在準則運算式中使用日期和相關函數。如需有關輸入及使用日期值的詳細資訊,請參閱輸入日期或時間值一文。

欄位

運算式

描述

ShippedDate

#2/2/2017#

顯示 2017 年 2 月 2 日出貨的訂單。

ShippedDate

Date()

顯示今天出貨的訂單。

RequiredDate

Between Date( ) And DateAdd("m", 3, Date( ))

使用 Between...And 運算子和 DateAdd 以及 Date 函數顯示今天起三個月內要出貨的訂單。

OrderDate

< Date( ) - 30

使用 Date 函數顯示超過 30 天前的訂單。

OrderDate

Year([OrderDate])=2017

使用 Year 函數顯示訂單日期為 2017 年的訂單。

OrderDate

DatePart("q", [OrderDate])=4

使用 DatePart 函數顯示第四季的訂單。

OrderDate

DateSerial(Year ([OrderDate]), Month([OrderDate])+1, 1)-1

使用 DateSerial、Year 和 Month 函數顯示每個月最後一天的訂單。

OrderDate

Year([OrderDate])= Year(Now()) And Month([OrderDate])= Month(Now())

使用 Year 和 Month 函數及 And 運算子顯示今年度本月份的訂單。

ShippedDate

Between #1/5/2017# And #1/10/2017#

使用 Between...And 運算子顯示介於 2017 年 1 月 5 日和 2017 年 1 月 10 日之間出貨的訂單。

RequiredDate

Between Date( ) And DateAdd("M", 3, Date( ))

使用 Between...And 運算子顯示今天起三個月內要出貨的訂單。

BirthDate

Month([BirthDate])=Month(Date())

使用 Month 和 Date 函數顯示本月生日的員工。

頁面頂端

尋找遺失的資料

下表中的運算式處理可能遺漏資訊的欄位,這類欄位可能包含 Null 值或零長度字串。Null 值代表遺漏的資訊,並不代表零或任何值。對資料庫的完整度而言,資訊遺漏是相當重要的概念,因此 Access 在產品設計中支援這個邏輯。在真實世界中,資訊遺漏的情形層出不窮,即使只是暫時性的闕漏也是屢見不鮮 (像是新產品的價格未定)。因此以真實世界實體 (例如業務) 為原型的資料庫也必須要能記錄遺漏的資訊。您可以使用 IsNull 函數來判斷欄位或控制項是否包含 Null 值,然後使用 Nz 函數將 Null 值轉換為零。

欄位

運算式

描述

ShipRegion

Is Null

顯示客戶 ShipRegion 欄位是 Null (遺漏) 值的訂單。

ShipRegion

Is Not Null

顯示客戶 ShipRegion 欄位含有值的訂單。

Fax

""

顯示沒有傳真機的客戶訂單,並以零長度字串值在 Fax 欄位中顯示,而非使用 Null (遺漏) 值。

頁面頂端

使用 Like 比對記錄模式

當您試著比對遵循模式的資料列時,Like 運算子可提供許多彈性,因為您可以使用 Like 搭配萬用字元來定義 Access 要比對的模式。舉例來說,* (星號) 萬用字元可比對任何類型的字元序列,方便您找出所有以該字母開頭的名稱。例如,您可以使用運算式 Like "S*" 來找出所有字母開頭為 S 的名稱。如需詳細資訊,請參閱 Like 運算子一文。

欄位

運算式

描述

ShipName

Like "S*"

尋找 ShipName 欄位中以字母 S 開頭的所有記錄。

ShipName

Like "*Imports"

尋找 ShipName 欄位中以 "Imports" 字詞結尾的所有記錄。

ShipName

Like "[A-D]*"

尋找 ShipName 欄位中,字母開頭為 A、B、C 或 D 的所有記錄。

ShipName

Like "*ar*"

尋找 ShipName 欄位中包含字母序列 "ar" 的所有記錄。

ShipName

Like "Maison Dewe?"

尋找 ShipName 欄位中符合下列條件的所有記錄:值的第一部分包含 "Maison",而第二部分則由 5 個字母字串組成,其中前 4 個字母為 "Dewe" 且最後一個字母不明。

ShipName

Not Like "A*"

尋找 ShipName 欄位中不以字母 A 開頭的所有記錄。

頁面頂端

使用 SQL 彙總函數來比較資料列

若需選擇性加總、計數或計算平均值,您可以使用 SQL 或範圍彙總函數。例如,若您只需計算落入特定範圍內的值,或結果評估為 Yes 的值,就能使用這類運算式。有時候,您可能需要先查詢其他資料表的值,才能顯示該值。下表中的範例運算式利用範圍彙總函數來執行一組值的計算,再以此結果做為查詢準則。

欄位

運算式

描述

Freight

> (DStDev("[Freight]", "Orders") + DAvg("[Freight]", "Orders"))

使用 DStDev 和 DAvg 函數顯示超過平均運費的所有訂單,以及運費的標準差。

Quantity

> DAvg("[Quantity]", "[Order Details]")

使用 DAvg 函數顯示訂購數量超過平均訂購數量的產品。

頁面頂端

使用子查詢比對欄位

您可以使用子查詢 (又稱為巢狀查詢) 來計算值並做為準則使用。下表中的範例運算式會根據子查詢所傳回的結果來比對資料列。

欄位

運算式

顯示

UnitPrice

(SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "Aniseed Syrup")

價格和茴芹糖漿價格相同的產品。

UnitPrice

>(SELECT AVG([UnitPrice]) FROM [Products])

單價高於平均值的產品。

Salary

> ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*Manager*") OR ([Title] LIKE "*Vice President*"))

薪水高於所有職稱為「經理」或「副總裁」的每個業務代表的薪水。

OrderTotal: [UnitPrice] * [Quantity]

> (SELECT AVG([UnitPrice] * [Quantity]) FROM [Order Details])

總金額高於平均訂單價值的訂單。

頁面頂端

更新查詢

您可以使用更新查詢來修改資料庫中一或多個現有欄位中的資料。例如,您可以整個取代或刪除所有相符的值。此表格會示範在更新查詢中使用運算式的幾種方式。您可以在查詢設計格線中的 [更新至] 資料列中,針對您要更新的欄位使用下列運算式。

如需有關建立更新查詢的詳細資訊,請參閱建立和執行更新查詢一文。

欄位

運算式

結果

Title

"Salesperson"

將文字值變更為銷售人員。

ProjectStart

#8/10/17#

將日期值變更為 2017 年 8 月 10 日。

Retired

Yes

將 Yes/No 欄位從 No 值變更為 Yes。

PartNumber

"PN" & [PartNumber]

在每個指定的零件編號開頭加上 PN。

LineItemTotal

[UnitPrice] * [Quantity]

計算 UnitPrice 和 Quantity 相乘的結果。

Freight

[Freight] * 1.5

增加百分之 50 的運費。

Sales

DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])

若目前資料表中的 ProductID 值與訂單詳細資料資料表中的 ProductID 值相符,就根據 Quantity 和 UnitPrice 相乘的結果更新銷售總額。

ShipPostalCode

Right([ShipPostalCode], 5)

截斷最左邊的字元,保留最後 5 個字元。

UnitPrice

Nz([UnitPrice])

在 UnitPrice 欄位中將 Null (未定義或未知) 值變更為零 (0)。

頁面頂端

SQL 陳述式

Access 的查詢語言採用結構化查詢語言 (SQL)。您在查詢設計檢視中建立的任何查詢,都能以 SQL 表達。若要查看任何查詢的 SQL 陳述式,請按一下 [檢視] 功能表上的 [SQL 檢視]。下表說明使用運算式的範例 SQL 陳述式。

使用運算式的 SQL 陳述式

結果

SELECT [FirstName],[LastName] FROM [Employees] WHERE [LastName]="Danseglio";

針對姓氏為「鐘」的員工顯示其 FirstName 欄位和 LastName 欄位中的值。

SELECT [ProductID],[ProductName] FROM [Products] WHERE [CategoryID]=Forms![New Products]![CategoryID];

若記錄中的 CategoryID 值與開啟的 New Products 表單中特定的 CategoryID 值相符,就顯示 Products 資料表中 ProductID 和 ProductName 欄位中的值。

SELECT Avg([ExtendedPrice]) AS [Average Extended Price] FROM [Order Details Extended] WHERE [ExtendedPrice]>1000;

針對 ExtendedPrice 欄位值大於 1000 的訂單計算平均總價,然後在名為 Average Extended Price 的欄位中顯示。

SELECT [CategoryID], Count([ProductID]) AS [CountOfProductID] FROM [Products] GROUP BY [CategoryID] HAVING Count([ProductID])>10;

在名為 CountOfProductID 的欄位中,針對具有 10 個以上產品的類別計算其產品總數並加以顯示。

頁面頂端

資料表運算式

在資料表中,兩種最常見的運算式使用方式為指派預設值以及建立驗證規則。

欄位預設值

設計資料庫時,建議您為欄位或控制項指派預設值。Access 之後就會在新記錄包含已建立欄位、或物件包含已建立控制項時提供預設值。下表中的運算式代表欄位或控制項的預設值範例。如果控制項與資料表欄位相連,且欄位具有預設值,則會優先使用控制項的預設值。

欄位

運算式

預設欄位值

Quantity

1

1

Region

"MT"

MT

Region

"New York, N.Y."

紐約, 紐約州 (請注意,若值含有標點符號,就必須以引號括住)。

Fax

""

根據預設,零長度字串代表欄位是空白而非 Null 值。

Order Date

Date( )

今天的日期

DueDate

Date() + 60

今天以後第 60 天的日期

頁面頂端

欄位驗證規則

您可以使用運算式為欄位或控制項建立驗證規則。Access 會在資料輸入到欄位或控制項時強制執行這項規則。若要建立驗證規則,請修改欄位或控制項的 ValidationRule 屬性。您也可以考慮設定 ValidationText 屬性,所設定的屬性文字會供 Access 在違反驗證規則時顯示。如果您未設定 ValidationText 屬性,Access 會顯示預設的錯誤訊息。

下表範例示範適用於 ValidationRule 屬性的驗證規則運算式,以及適用於 ValidationText 屬性的相關文字。

ValidationRule 屬性

ValidationText 屬性

<> 0

請輸入非零值。

0 Or > 100

值必須是 0 或大於 100。

Like "K???"

值必須是以字母 K 開頭的四個字元。

< #1/1/2017#

輸入的日期必須早於 2017 年 1 月 1 日。

>= #1/1/2017# And < #1/1/2008#

日期必須在 2017 年內。

如需驗證資料的詳細資訊,請參閱建立驗證規則以驗證欄位中的資料一文。

頁面頂端

巨集運算式

在某些情況下,只有在特定條件為 True 時,才建議您執行單一指令或以巨集執行一連串指令。舉例來說,假設您只想在 Counter 文字方塊為 10 時才執行某個指令,您可在巨集的 [條件] 欄中使用運算式來定義條件:

[Counter]=10

ValidationRule 屬性相同,[條件] 欄運算式也採用條件運算式。它必須判斷值為 TrueFalse。只有當條件為 True 時,系統才會執行指令。

祕訣   :若要強制 Access 暫時略過指令,請將 False 輸入為條件。這在您嘗試解決巨集出錯時會非常有用。

使用這個運算式來執行指令

If

[City]="Paris"

若表單欄位中的 City 值為巴黎,系統就會執行巨集。

DCount("[OrderID]", "Orders") > 35

若 Orders 資料表中的 [OrderID] 欄位超過 35 個項目。

DCount("*", "[Order Details]", "[OrderID]=" & Forms![Orders]![OrderID]) > 3

若 Order Details 資料表中有超過 3 個項目的 OrderID 欄位與 Orders 表單的 OrderID 欄位相符。

[ShippedDate] Between #2-Feb-2017# And #2-Mar-2017#

若表單上 [ShippedDate] 欄位的值介於 2017 年 2 月 2 日和 2017 年 3 月 2 日之間時,系統就會執行巨集。

Forms![Products]![UnitsInStock] < 5

Products 表單上的 [UnitsInStock] 欄位值小於 5。

IsNull([FirstName])

若表單上的 FirstName 值為 Null (沒有值) 時,系統就會執行巨集。也就是說,此運算式的作用等同於 [FirstName] 為 Null。

[CountryRegion]="UK" And Forms![SalesTotals]![TotalOrds] > 100

若表單上 CountryRegion 欄位值為英國,且 SalesTotals 表單上的 TotalOrds 欄位值大於 100 時,系統就會執行巨集。

[CountryRegion] In ("France", "Italy", "Spain") And Len([PostalCode])<>5

若表單上 CountryRegion 欄位值為法國、義大利或西班牙,且郵遞區號長度不是 5 個字元時,系統就會執行巨集。

MsgBox("Confirm changes?",1)=1

若您在 MsgBox 函數所顯示的對話方塊中按一下 [確定]。若您在對話方塊中按一下 [取消],Access 就會略過指令。

頁面頂端

請參閱

使用運算式建立器

運算式簡介

建立運算式

運算式語法指南

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×