ACCESS VBA編程

1、ACCESS VBA編程（六）ACCESS查詢分段統(tǒng)計人數(shù)這樣一個表 tblScore：班級 姓名 總分 語文 數(shù)學1班 a 601 108 1202班 b 589 112 1333班 C 551 98 1452班 D 502 80 1241班 E 508 90 83班 F 561 97 135 TRANSFORM Count(tblScore.總分) AS 總分OfCountSelect tblScore.班級FROM tblScoreGROUP BY tblScore.班級PIVOT Switch(總分=600,=600,總分=550 And 總分=500 And 總分=600,550-5

2、99,500-549,Other);可得到第一個查詢班級 總分600分以上人數(shù) 總分550-600人數(shù) 總分550以下人數(shù)1班 1 0 12班 0 1 13班 0 2 0用代碼在ACCESS中生成永久查詢來源：竹笛整理的技巧集dim strSQL as stringdim qdf as QueryDefstrSQL = Select * from tblaa tblaa為表Set qdf = CurrentDb.CreateQueryDef(創(chuàng)建的查詢, strSQL)DoCmd.OpenQuery qdf.Name用代碼刪除一個已存在的查詢來源：愛賽思應用俱樂部 wxjgwDim Query

3、1 As QueryDefCurrentDb.QueryDefs.RefreshFor Each Query1 In CurrentDb.QueryDefsIf Query1.Name = 想要刪除的查詢名稱 ThenCurrentDb.QueryDefs.Delete Query1.NameExit ForEnd IfNext Query1使用ADO和SQL語句建立一個新查詢來源：ACCESS中國 huanghaiDim cat As New ADOX.CatalogDim cmd As New ADODB.CommandSet cat.ActiveConnection = CurrentP

4、roject.Connectioncmd.CommandText = Select * FROM 表1cat.Views.Append newView, cmd以窗體的文體框為條件進行模糊查詢時查詢的設計視圖中準則：Like IIf(IsNull(Forms!存書查詢窗體!作者),*,* & Forms!存書查詢窗體!作者 & *)用VBA代碼生成一個條件組合的字符串作為子窗體的窗體篩選的條件來實現(xiàn)窗體的多條件查詢。Option Compare Database由淺入深的介紹幾種最常用的利用主/子窗體來實現(xiàn)查詢的方法，使初學者和有一定VBA基礎的人可以更好的使用窗體查詢這種手段。本例程是講解用

5、VBA代碼生成一個條件組合的字符串作為子窗體的窗體篩選的條件來實現(xiàn)窗體的多條件查詢。Private Sub cmd查詢_Click()On Error GoTo Err_cmd查詢_ClickDim strWhere As String 定義條件字符串strWhere = 設定初始值空字符串判斷【書名】條件是否有輸入的值If Not IsNull(Me.書名) Then有輸入strWhere = strWhere & (書名 like * & Me.書名 & *) AND End If判斷【類別】條件是否有輸入的值If Not IsNull(Me.類別) Then有輸入strWhere = s

6、trWhere & (類別 like & Me.類別 & ) AND End If判斷【作者】條件是否有輸入的值If Not IsNull(Me.作者) Then 有輸入strWhere = strWhere & (作者 like * & Me.作者 & *) AND End If判斷【出版社】條件是否有輸入的值If Not IsNull(Me.出版社) Then有輸入strWhere = strWhere & (出版社 like & Me.出版社 & ) AND End If判斷【單價】條件是否有輸入的值，由于有【單價開始】【單價截止】兩個文本框所以要分開來考慮If Not IsNull(M

7、e.單價開始) Then【單價開始】有輸入strWhere = strWhere & (單價 = & Me.單價開始 & ) AND End IfIf Not IsNull(Me.單價截止) Then【單價截止】有輸入strWhere = strWhere & (單價 = # & Format(Me.進書日期開始, yyyy-mm-dd) & #) AND End IfIf Not IsNull(Me.進書日期截止) Then【進書日期截止】有輸入strWhere = strWhere & (進書日期 0 Then有輸入條件strWhere = Left(strWhere, Len(strWh

8、ere) - 5)End If先在立即窗口顯示一下strWhere的值，代碼調(diào)試完成后可以取消下一句Debug.Print strWhere讓子窗體應用窗體查詢Me.存書查詢子窗體.Form.Filter = strWhereMe.存書查詢子窗體.Form.FilterOn = True在子窗體篩選后要運行一下自編子程序CheckSubformCount()Call CheckSubformCountExit_cmd查詢_Click:Exit SubErr_cmd查詢_Click:MsgBox Err.DescriptionResume Exit_cmd查詢_ClickEnd SubPriva

9、te Sub cmd導出_Click()On Error GoTo Err_cmd導出_Click這里將使用DAO來改變查詢的SQL語句，必須先在“工具”“引用”中選擇Microsoft DAO 3.6 Object Library.Dim qdf As DAO.QueryDef qdf被定義為一個查詢定義對象Dim strWhere, strSQL As StringstrWhere = Me.存書查詢子窗體.Form.FilterIf strWhere = Then沒有條件strSQL = Select * FROM 存書查詢Else有條件strSQL = Select * FROM 存書

10、查詢 Where & strWhereEnd IfSet qdf = CurrentDb.QueryDefs(查詢結果)qdf.SQL = strSQLqdf.CloseSet qdf = NothingDoCmd.OutputTo acOutputQuery, 查詢結果, acFormatXLS, , TrueExit_cmd導出_Click:Exit SubErr_cmd導出_Click:MsgBox Err.DescriptionResume Exit_cmd導出_ClickEnd SubPrivate Sub cmd清除_Click()On Error GoTo Err_cmd清除_C

11、lick劉小軍(Alex) 2003-5-22這里將使用FOR EACH CONTROL的方法來清除控件的值這在控件比較多的時候非常有用。Dim ctl As ControlFor Each ctl In Me.Controls根據(jù)ctl的控件類型來選擇Select Case ctl.ControlTypeCase acTextBox 是文本框，要清空(注意，子窗體下面還有兩個鎖定的文本框不能賦值)If ctl.Locked = False Then ctl.Value = NullCase acComboBox 是組合框，也要清空ctl.Value = Null其它類型的控件不處理End S

12、electNext取消子窗體的篩選Me.存書查詢子窗體.Form.Filter = Me.存書查詢子窗體.Form.FilterOn = False在子窗體取消篩選后要運行一下自編子程序CheckSubformCount()Call CheckSubformCountExit_cmd清除_Click:Exit SubErr_cmd清除_Click:MsgBox Err.DescriptionResume Exit_cmd清除_ClickEnd SubPrivate Sub cmd預覽報表_Click()On Error GoTo Err_cmd預覽報表_ClickDim stDocName,

13、strWhere As StringstDocName = 藏書情況報表strWhere = Me.存書查詢子窗體.Form.Filter在打開報表的同時把子窗體的篩選條件字符串也傳遞給報表，這樣地話報表也會顯示和子窗體相同的記錄。DoCmd.OpenReport stDocName, acPreview, , strWhereExit_cmd預覽報表_Click:Exit SubErr_cmd預覽報表_Click:MsgBox Err.DescriptionResume Exit_cmd預覽報表_ClickEnd SubPrivate Sub CheckSubformCount()這是一個自

14、編子程序，專門用來檢查子窗體上的記錄數(shù)，以便修改主窗體上的“計數(shù)”和“合計”的控件來源，以防止出現(xiàn)“#錯誤”。If Me.存書查詢子窗體.Form.Recordset.RecordCount 0 Then子窗體的記錄數(shù)0Me.計數(shù).ControlSource = =存書查詢子窗體.Form.txt計數(shù)Me.合計.ControlSource = =存書查詢子窗體.Form.txt單價合計Else子窗體的記錄數(shù)=0Me.計數(shù).ControlSource = =0Me.合計.ControlSource = =0End IfEnd Sub用VBA代碼+DAO生成帶條件的交叉表查詢Option Comp

15、are Database由淺入深的介紹幾種最常用的利用主/子窗體來實現(xiàn)查詢的方法，使初學者和有一定VBA基礎的人可以更好的使用窗體查詢這種手段。本例程是講解用VBA代碼+DAO生成帶條件的交叉表查詢。Private Sub cmd查詢_Click()On Error GoTo Err_cmd查詢_ClickDim strWhere As String 定義條件字符串Dim qdf As DAO.QueryDef qdf被定義為一個查詢定義對象Dim strSQL As StringstrWhere = 設定初始值空字符串判斷【類別】條件是否有輸入的值If Not IsNull(Me.類別) T

16、hen有輸入strWhere = strWhere & (類別 like & Me.類別 & ) AND End If判斷【出版社】條件是否有輸入的值If Not IsNull(Me.出版社) Then有輸入strWhere = strWhere & (出版社 like & Me.出版社 & ) AND End If判斷【單價】條件是否有輸入的值，由于有【單價開始】【單價截止】兩個文本框所以要分開來考慮If Not IsNull(Me.單價開始) Then【單價開始】有輸入strWhere = strWhere & (單價 = & Me.單價開始 & ) AND End IfIf Not Is

17、Null(Me.單價截止) Then【單價截止】有輸入strWhere = strWhere & (單價 = # & Format(Me.進書日期開始, yyyy-mm-dd) & #) AND End IfIf Not IsNull(Me.進書日期截止) Then【進書日期截止】有輸入strWhere = strWhere & (進書日期 0 Then有輸入條件strWhere = Left(strWhere, Len(strWhere) - 5)End If先在立即窗口顯示一下strWhere的值，代碼調(diào)試完成后可以取消下一句Debug.Print strWhere根據(jù)是否有條件來設定交叉

18、表查詢的SQL語句If Len(strWhere) 0 ThenstrSQL = TRANSFORM Sum(存書查詢.單價) AS 單價之Sum Select 存書查詢.類別 FROM 存書查詢 strSQL = strSQL & Where( & strWherestrSQL = strSQL & ) GROUP BY 存書查詢.類別 PIVOT Format(進書日期,yyyy/mm)ElsestrSQL = TRANSFORM Sum(存書查詢.單價) AS 單價之Sum & _ Select 存書查詢.類別 & _ FROM 存書查詢 & _ GROUP BY 存書查詢.類別 & _

19、 PIVOT Format(進書日期,yyyy/mm)End If修改交叉表查詢的SQL語句Set qdf = CurrentDb.QueryDefs(存書查詢_交叉表)qdf.SQL = strSQLqdf.CloseSet qdf = Nothing顯示交叉表的內(nèi)容，不能直接刷新Me.存書查詢子窗體.SourceObject = Me.存書查詢子窗體.SourceObject = 查詢.存書查詢_交叉表刷新計數(shù)和合計顯示Me.計數(shù) = DCount(*, 存書查詢_交叉表)Me.合計 = DSum(單價, 存書查詢, strWhere)Exit_cmd查詢_Click:Exit SubEr

20、r_cmd查詢_Click:MsgBox Err.DescriptionResume Exit_cmd查詢_ClickEnd SubPrivate Sub cmd導出_Click()On Error GoTo Err_cmd導出_Click由于前面我們已經(jīng)通過DAO修改了“存書查詢_交叉表”的SQL語句，所以這里我們直接導出就可以了。DoCmd.OutputTo acOutputQuery, 存書查詢_交叉表, acFormatXLS, , TrueExit_cmd導出_Click:Exit SubErr_cmd導出_Click:MsgBox Err.DescriptionResume Exi

21、t_cmd導出_ClickEnd SubPrivate Sub cmd清除_Click()On Error GoTo Err_cmd清除_Click這里將使用FOR EACH CONTROL的方法來清除控件的值這在控件比較多的時候非常有用。Dim ctl As ControlDim qdf As DAO.QueryDef qdf被定義為一個查詢定義對象Dim strSQL As StringFor Each ctl In Me.Controls根據(jù)ctl的控件類型來選擇Select Case ctl.ControlTypeCase acTextBox 是文本框，要清空(注意，子窗體下面還有兩個

22、鎖定的文本框不能賦值)If ctl.Locked = False Then ctl.Value = NullCase acComboBox 是組合框，也要清空ctl.Value = Null其它類型的控件不處理End SelectNextstrSQL = TRANSFORM Sum(存書查詢.單價) AS 單價之Sum & _ Select 存書查詢.類別 & _ FROM 存書查詢 & _ GROUP BY 存書查詢.類別 & _ PIVOT Format(進書日期,yyyy/mm)修改交叉表查詢的SQL語句Set qdf = CurrentDb.QueryDefs(存書查詢_交叉表)qdf

23、.SQL = strSQLqdf.CloseSet qdf = Nothing顯示交叉表的內(nèi)容，不能直接刷新Me.存書查詢子窗體.SourceObject = Me.存書查詢子窗體.SourceObject = 查詢.存書查詢_交叉表刷新計數(shù)和合計顯示Me.計數(shù) = DCount(*, 存書查詢_交叉表)Me.合計 = DSum(單價, 存書查詢)Exit_cmd清除_Click:Exit SubErr_cmd清除_Click:MsgBox Err.DescriptionResume Exit_cmd清除_ClickEnd SubPrivate Sub cmd預覽報表_Click()On Er

24、ror GoTo Err_cmd預覽報表_ClickDim stDocName, strWhere As StringstDocName = 藏書情況報表DoCmd.OpenReport stDocName, acViewPreviewExit_cmd預覽報表_Click:Exit SubErr_cmd預覽報表_Click:MsgBox Err.DescriptionResume Exit_cmd預覽報表_ClickEnd SubPrivate Sub Form_Open(Cancel As Integer)如果沒有這一段代碼，窗體打開時，雖然子窗體有顯示，但下面的兩個文本框是空的。刷新計數(shù)和

25、合計顯示Me.計數(shù) = DCount(*, 存書查詢_交叉表)Me.合計 = DSum(單價, 存書查詢)End Sub*在報表的打開事件中寫：Private Sub Report_Open(Cancel As Integer)根據(jù)交叉表查詢的實際字段數(shù)來設定報表各節(jié)可以顯示的控件數(shù)。需要使用DAO 3.6Dim rst As DAO.Recordset, intFieldsNum As Integer, I As Integer打開查詢Set rst = CurrentDb.OpenRecordset(Select * FROM 存書查詢_交叉表 Where 1=2)rst.MoveLast

26、rst.MoveFirstDebug.Print rst.RecordCount記錄字段總數(shù)intFieldsNum = rst.Fields.Count由于報表僅有10個可變字段1個固定字段，所以，如果字段總數(shù)11時，只顯示前面的11個字段，并給出提示。If intFieldsNum 11 ThenintFieldsNum = 11MsgBox 字段總數(shù)太多，報表僅顯示前11個字段。, vbInformation + vbOKOnly, 提示End IfFor I = 1 To 10If I = (intFieldsNum - 1) Then有對應字段，rst.Fields(I) 中 rst

27、.Fields(0)是第一個，是“類別”字段。頁眉標簽可見Section(acPageHeader).Controls(標簽 & I).Caption = rst.Fields(I).NameSection(acPageHeader).Controls(標簽 & I).Visible = True主體字段可見Section(acDetail).Controls(txt & I).ControlSource = rst.Fields(I).NameSection(acDetail).Controls(txt & I).Visible = True報表頁腳合計可見Section(acFooter)

28、.Controls(txt合計 & I).ControlSource = =SUM(NZ( & rst.Fields(I).Name & ,0)Section(acFooter).Controls(txt合計 & I).Visible = TrueElse沒有對應字段頁眉標簽不可見Section(acPageHeader).Controls(標簽 & I).Visible = False主體字段不可見Section(acDetail).Controls(txt & I).ControlSource = Section(acDetail).Controls(txt & I).Visible =

29、False報表頁腳合計可見Section(acFooter).Controls(txt合計 & I).ControlSource = Section(acFooter).Controls(txt合計 & I).Visible = FalseEnd IfNextrst.CloseSet rst = NothingEnd Sub進行多條件查詢, 希望某一條件為空時顯示全部where name1 like *temp1* and name2 like *temp2*如何判斷奇數(shù)（單數(shù)）、偶數(shù)（雙數(shù)）？dim a as string(這里有一段給a賦值的代碼）if a mod 2=0 thenmsgb

30、ox這是一個偶數(shù)eslemsgbox這是一個奇數(shù)end if計算在每個范圍內(nèi)的數(shù)量本示例假設您有一個“Orders”表，且里頭含有一個“Freight”字段。程序建立一個“選擇”來計算運費落在某些范圍內(nèi)的訂單數(shù)量。Partition 函數(shù)是用來確定這些范圍，然后調(diào)用 SQL Count 函數(shù)來計算在每個范圍內(nèi)的訂單數(shù)量。本示例中，Partition 函數(shù)的參數(shù)值為 start = 0，stop = 500，interval = 50。第一個范圍會是 0：49，每隔 50 一個范圍，依次而下直到運費為 500 為止。Select DISTINCTROW Partition(freight,0,

31、500, 50) AS Range,Count(Orders.Freight) AS CountFROM ordersGROUP BY Partition(freight,0,500,50);使用 Trim 函數(shù)顯示字段的值，并且刪除首尾的空格。使用 Trim 函數(shù)顯示“地址”字段的值，并且刪除首尾的空格。=Trim(地址)Like函數(shù)示例：查詢條件為“Like * & forms!銷售單輸入!文本26”，當我輸入60時，所有包含60的記錄全部得出，諸如160、260、360等只想要60的記錄，并且當不輸入任何數(shù)據(jù)時，所有記錄全部得出Like IIf(forms!銷售單輸入!文本26 Is N

32、ot Null,forms!銷售單輸入!文本26,*)使用 Left 函數(shù)來得到某字符串最左邊的幾個字符。Dim AnyString, MyStrAnyString = Hello World 定義字符串。MyStr = Left(AnyString, 1) 返回 H。MyStr = Left(AnyString, 7) 返回 Hello W。MyStr = Left(AnyString, 20) 返回 Hello World。使用 Mid 語句來得到某個字符串中的幾個字符。Dim MyString, FirstWord, LastWord, MidWordsMyString = Mid Fu

33、nction Demo 建立一個字符串。FirstWord = Mid(MyString, 1, 3) 返回 Mid。LastWord = Mid(MyString, 14, 4) 返回 Demo。MidWords = Mid(MyString, 5) 返回 Funcion Demo。使用 Right 函數(shù)來返回某字符串右邊算起的幾個字符。Dim AnyString, MyStrAnyString = Hello World 定義字符串。MyStr = Right(AnyString, 1) 返回 d。MyStr = Right(AnyString, 6) 返回 World。MyStr = R

34、ight(AnyString, 20) 返回 Hello World。使用 InStr 函數(shù)來查找某字符串在另一個字符串中首次出現(xiàn)的位置。Dim SearchString, SearchChar, MyPosSearchString =XXpXXpXXPXXP 被搜索的字符串。SearchChar = P 要查找字符串 P。 從第四個字符開始，以文本比較的方式找起。返回值為 6（小寫 p）。 小寫 p 和大寫 P 在文本比較下是一樣的。MyPos = Instr(4, SearchString, SearchChar, 1) 從第一個字符開使，以二進制比較的方式找起。返回值為 9（大寫 P）。

35、 小寫 p 和大寫 P 在二進制比較下是不一樣的。MyPos = Instr(1, SearchString, SearchChar, 0) 缺省的比對方式為二進制比較（最后一個參數(shù)可省略）。MyPos = Instr(SearchString, SearchChar) 返回 9。MyPos = Instr(1, SearchString, W) 返回 0。使用 Space 函數(shù)來生成一個字符串，字符串的內(nèi)容為空格，長度為指定的長度。Dim MyString 返回 10 個空格的字符串。MyString = Space(10) 將 10 個空格插入兩個字符串中間。MyString = Hello & Space(10) & World使用 String 函數(shù)來生成一指定長度，且只含單一字符的字符串。Dim MyStringMyString = String(5, *) 返回 *。MyString = String(5, 42) 返回 *。MyString = String(10, ABC) 返回 AAAAAAAAAA。使用 DLook