ACCESS-VBA编程
使用谓词（如将联接与常量比较）可以进一步限制外联接。下例包含相同的右向外联接，但消除销售量低于 50 本的书籍的书名： USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t ON s.title_id = t.title_id AND s.qty > 50
ORDER BY s.stor_id ASC 下面是结果集： stor_id qty title
------- ------ --------------------------------------------------------- (null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets (null) (null) Emotional Security: A New Algorithm
(null) (null) Fifty Years in Buckingham Palace Kitchens 7066 75 Is Anger the Enemy? (null) (null) Life Without Fear (null) (null) Net Etiquette
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
(null) (null) Prolonged Data Deprivation: Four Case Studies (null) (null) Secrets of Silicon Valley
(null) (null) Silicon Valley Gastronomic Treats (null) (null) Straight Talk About Computers (null) (null) Sushi, Anyone?
(null) (null) The Busy Executive's Database Guide (null) (null) The Gourmet Microwave
(null) (null) The Psychology of Computer Cooking (null) (null) You Can Combat Computer Stress! (18 row(s) affected)
有关谓词的更多信息，请参见 WHERE。 使用完整外部联接
若要通过在联接结果中包括不匹配的行保留不匹配信息，请使用完整外部联接。Microsoft? SQL Server
? 2000 提供完整外部联接运算符 FULL OUTER JOIN，不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。
假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者（本例中为 Abraham Bennet 和 Cheryl Carson）。SQL-92 FULL OUTER JOIN 运算符指明：不管表中是否有匹配的数据，结果将包括两个表中的所有行。
若要在结果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同一个城市，请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果： USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集：
au_fname au_lname pub_name
-------------------- ---------------------------- -------------------- Reginald Blotchet-Halls NULL
36
ACCESS-VBA编程
Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL
Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL
Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing NULL NULL GGG&G
NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (30 row(s) affected)
数字金额转换为大写的函数
函数只支持到千亿,如果需要,可以增加 CU 数组的长度,算法不变. '将数字转换为大写
Public Function MoneyConv(Money As Currency) As String On Error GoTo Doerr Dim CN(9) As String Dim CU(15) As String
Dim Temp As String, strNum As String Dim CM As String
Dim tFirst As String, tEnd As String Dim i As Long, j As Long, k As Long CN(0) = \零\ CN(1) = \壹\ CN(2) = \贰\ CN(3) = \叁\ CN(4) = \肆\ CN(5) = \伍\ CN(6) = \陆\ CN(7) = \柒\ CN(8) = \捌\
37
ACCESS-VBA编程
CN(9) = \玖\
' CU(0) = \分\' CU(1) = \角\ CU(0) = \圆\ CU(1) = \十\ CU(2) = \佰\ CU(3) = \仟\ CU(4) = \万\ CU(5) = \十\ CU(6) = \佰\ CU(7) = \仟\ CU(8) = \亿\ CU(9) = \十\ CU(10) = \佰\ CU(11) = \仟\
If Money = 0 Then CM = \零圆整\ GoTo Complete End If
strNum = Trim(str(FormatCurrency(Money, 2, vbTrue, vbFalse, vbFalse))) If Left(strNum, 1) = \ tFirst = \负\
strNum = Right(strNum, Len(strNum) - 1) Else
tFirst = \ End If
i = InStrRev(strNum, \ If i <> 0 Then
Temp = Right(strNum, i)
If Len(strNum) - i = 1 Then Temp = Temp + \
CM = CN(CInt(Left(Right(Temp, 2), 1))) + \角\分\ tEnd = \
strNum = Left(strNum, i - 1) Else
tEnd = \整\ End If
i = 0
For j = Len(strNum) To 1 Step -1
k = CInt(Right(Left(strNum, j), 1)) If k = 0 Then
If i <> 0 And i <> 4 And i <> 8 Then CM = CN(k) + CM Else
CM = CN(k) + CU(i) + CM End If Else
CM = CN(k) + CU(i) + CM
38
ACCESS-VBA编程
End If
' CM = CN(k) + CU(i) + CM i = i + 1 Next j
CM = tFirst + CM + tEnd
CM = Replace(CM, \零零\零\ CM = Replace(CM, \零零\零\
CM = Replace(CM, \亿零万零圆\亿圆\ CM = Replace(CM, \亿零万\亿零\ CM = Replace(CM, \万零圆\万圆\ CM = Replace(CM, \零亿\亿\ CM = Replace(CM, \零万\万\ CM = Replace(CM, \零圆\圆\ CM = Replace(CM, \零零\零\
CM = Replace(CM, \零零\零\重复替换一次 Complete:
Gerr = 0 '操作成功,无错误发生 MoneyConv = CM Exit Function Doerr:
Gerr = -1 '未知错误 Errexit:
MoneyConv = \End Function
数字货币转换为大写格式
以下为数字货币转换为大写格式程序, 首先建一个模块, 将以下程序复制进去并保存. (位)
调用方式为:
dollars = chMoney(Val(inputValue)) ^ ^ 须显示 填写小 大写的 写的控 控件 件名
' 名称： CCh
' 得到一位数字 N1 的汉字大写 ' 0 返回 \
Public Function CCh(N1) As String Select Case N1 Case 0
CCh = \零\ Case 1
CCh = \壹\ Case 2
CCh = \贰\ Case 3
CCh = \叁\ Case 4
CCh = \肆\
39
注: 最高位数为千万ACCESS-VBA编程
Case 5
CCh = \伍\ Case 6
CCh = \陆\ Case 7
CCh = \柒\ Case 8
CCh = \捌\ Case 9
CCh = \玖\End Select End Function
'名称： ChMoney
' 得到数字 N1 的汉字大写。最大为 千万位。 O 返回 Public Function chMoney(N1) As String Dim tMoney As String Dim lMoney As String Dim tn '小数位置
Dim s1 As String '临时STRING 小数部分 Dim s2 As String '1000 以内 Dim s3 As String '10000 Dim st1, t1
If N1 = 0 Then chMoney = \ Exit Function End If
If N1 < 0 Then
chMoney = \负\ Exit Function End If
tMoney = Trim(Str(N1))
tn = InStr(tMoney, \小数位置 s1 = \
If tn <> 0 Then
st1 = Right(tMoney, Len(tMoney) - tn) If st1 <> \ t1 = Left(st1, 1)
st1 = Right(st1, Len(st1) - 1) If t1 <> \
s1 = s1 + CCh(Val(t1)) + \角\ End If
If st1 <> \ t1 = Left(st1, 1)
s1 = s1 + CCh(Val(t1)) + \分\ End If End If
st1 = Left(tMoney, tn - 1) Else
40