Access - VBA编程（使用技巧大全）[1](9)

来源：网络收集时间：2020-06-07 下载这篇文档手机版

说明：文章内容仅供预览，部分内容可能不全，需要完整文档或者需要复制内容，请下载word后使用。下载word有问题请添加微信号:或QQ：处理（尽可能给您提供完整文档），感谢您的支持与谅解。

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

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) = \捌\

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

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 = \肆\

注: 最高位数为千万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

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库Access - VBA编程（使用技巧大全）[1](9)在线全文阅读。

Access - VBA编程（使用技巧大全）[1](9).doc 将本文的Word文档下载到电脑，方便复制、编辑、收藏和打印下载失败或者文档不完整，请联系客服人员解决！

下载这篇word文档

本文链接：https://www.77cn.com.cn/wenku/zonghe/1098440.html（转载请注明文章来源）

上一篇：打坐入定的各个阶段 
下一篇：品牌部绩效考核管理制度V1.0 - 图文