# 運算式的範例

## 表單和報表

### 文字運算

="N/A"

=[FirstName] & " " & [LastName]

=Left([ProductName], 1)

=Right([AssetCode], 2)

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

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

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

### 頁首及頁尾

=[Page]

1

="第 " & [Page] & " 頁"

="頁 " & [Page] & " / " & [Pages]

="第 " & [Page] & " 頁，共 " & [Pages] & " 頁"

=[Page] & "/" & [Pages] & " 頁"

1/3 頁

=[Country/region] & " - " & [Page]

=Format([Page], "000")

001

="列印日期：" & Date()

### 算術運算

=[Subtotal]+[Freight]

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

=[RequiredDate]-[ShippedDate]

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

=[Price]*1.06

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

=[Quantity]*[Price]

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

=[EmployeeTotal]/[CountryRegionTotal]

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

### 其他控制項中的值

=Forms![Orders]![OrderID]

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

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

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

=Parent![OrderID]

=Report![Invoice]![OrderID]

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

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

=Parent![OrderID]

### 計數、加總和平均值

=Avg([Freight])

=Count([OrderID])

=Sum([Sales])

=Sum([Quantity]*[Price])

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

### SQL 彙總函數

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

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

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

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

### 日期運算

=Date()

=Format(Now(), "ww")

=DatePart("yyyy", [OrderDate])

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

=[InvoiceDate] + 30

### 僅有兩個值的條件

=IIf([Confirmed] = "是", "訂單已確認", "訂單未確認")

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

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

=IIf(IsNull([RequiredDate]) Or IsNull([ShippedDate]), "檢查是否缺少日期", [RequiredDate] - [ShippedDate])

## 查詢和篩選

### 文字運算

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

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

ProductInitial:Left([ProductName], 1)

TypeCode:Right([AssetCode], 2)

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

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

### 算術運算

PrimeFreight: [Freight] * 1.1

OrderAmount: [Quantity] * [UnitPrice]

TotalStock: [UnitsInStock]+[UnitsOnOrder]

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

### 日期運算

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

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

MinusThirty:Date( )- 30

### SQL 彙總函數

RowCount:Count(*)

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

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

### 包含遺失資料的欄位

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

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

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

### 使用子查詢來導出欄位

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

### 比對文字值

ShipCity

"倫敦"

ShipCity

"倫敦" Or "赫奇恩德"

ShipCountryRegion

In("加拿大", "英國")

ShipCountryRegion

Not "美國"

ProductName

Not Like "C*"

CompanyName

>="N"

ProductCode

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

ShipName

Like "S*"

### 比較資料準則

ShippedDate

#2/2/2017#

ShippedDate

Date()

RequiredDate

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

OrderDate

< Date( ) - 30

OrderDate

Year([OrderDate])=2017

OrderDate

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

OrderDate

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

OrderDate

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

ShippedDate

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

RequiredDate

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

BirthDate

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

ShipRegion

Is Null

ShipRegion

Is Not Null

Fax

""

### 使用 Like 比對記錄模式

ShipName

Like "S*"

ShipName

Like "*Imports"

ShipName

Like "[A-D]*"

ShipName

Like "*ar*"

ShipName

Like "Maison Dewe?"

ShipName

Not Like "A*"

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

Freight

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

Quantity

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

### 使用子查詢比對欄位

UnitPrice

(SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "茴芹糖漿")

UnitPrice

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

Salary

> ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*經理*") OR ([Title] LIKE "*副總裁*"))

OrderTotal: [UnitPrice] * [Quantity]

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

### 更新查詢

"銷售人員"

ProjectStart

#8/10/17#

Yes

PartNumber

"PN" & [PartNumber]

LineItemTotal

[UnitPrice] * [Quantity]

Freight

[Freight] * 1.5

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

ShipPostalCode

Right([ShipPostalCode], 5)

UnitPrice

Nz([UnitPrice])

### 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;

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

## 資料表運算式

Quantity

1

1

Region

"MT"

MT

Region

"紐約, 紐約州"

Fax

""

Date( )

DueDate

Date() + 60

### 欄位驗證規則

ValidationRule 屬性

ValidationText 屬性

<> 0

0 Or > 100

Like "K???"

< #1/1/2017#

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

## 巨集運算式

``` [Counter]=10 ```

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

If

[City]="巴黎"

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

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

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

Forms![Products]![UnitsInStock] < 5

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

IsNull([FirstName])

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

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

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

×