|
发表于 2013-5-19 14:35
|
显示全部楼层
本楼为最佳答案
窗体代码。- Option Explicit
- Private Sub CommandButton1_Click()
- Dim strSql$
- Dim strFind$
- Dim strCondition1$
- Dim strCondition2$
- Dim Database As String
- On Error GoTo Errcheck
- Select Case True
- Case Me.OptionButton1.Value
- strFind = OptionButton1.Caption
- Case Me.OptionButton2.Value
- strFind = OptionButton2.Caption
- Case Me.OptionButton3.Value
- strFind = OptionButton3.Caption
- Case Me.OptionButton4.Value
- strFind = OptionButton4.Caption
- Case Else
- MsgBox "请选择要查找的内容"
- Exit Sub
- End Select
- Database = "data"
- strSql = "select 报销月份,序号,定点医疗机构名称,医保卡号,单位名称,姓名,性别," & _
- "年龄,入院日期,出院日期,住院天数,出院诊断,本次住院医疗费总额,甲类药费," & _
- "乙类药费,进口药费,自费药费,超出范围,进口材料费,国产材料费," & _
- "特殊检查费特殊治疗费,丙类项目,其它费用,起付段金额,个人政策自付小计," & _
- "自费药品及自费项目,实际结算自付,统筹基金支付,大病求助基金支付," & _
- "个人支付金额,本年住院次数,本年范围内费用累计,本年大病范围内费用累计 from " & Database
- Select Case True
- Case Len(Me.TextBox1.Text) > 0
- strCondition1 = " where " & strFind & "='" & Me.TextBox1.Text & "' "
- Case Else
- strCondition1 = " where " & strFind & " like '%' "
- End Select
- Select Case True
- Case Len(Me.TextBox2.Text) = 0 And Len(Me.TextBox3.Text) = 0
- Case Len(Me.TextBox2.Text) = 0
- strCondition2 = " and 录入时间<=#" & Me.TextBox3.Text & "#"
- Case Len(Me.TextBox3.Text) = 0
- strCondition2 = " and 录入时间>=#" & Me.TextBox2.Text & "#"
- Case Else
- strCondition2 = " and 录入时间 between #" & Me.TextBox2.Text & "# and #" & Me.TextBox3.Text & "#"
- End Select
- MsgBox strSql & strCondition1 & strCondition2
- Call ADOQuery(strSql & strCondition1 & strCondition2)
- Exit Sub
-
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Sub
- Private Sub CommandButton2_Click()
- Unload Me
- End Sub
- Sub ADOQuery(strSql As String)
- Dim AdoConn As Object, AdoRst As Object
- Dim StrConn$
- Dim AccessFile As String
-
-
- AccessFile = ThisWorkbook.Path & "\data.mdb"
- If Dir(AccessFile) = "" Then
- MsgBox "ACCESS数据文件不存在"
- Exit Sub
- End If
- Range("A6:AG65536").ClearContents
- With Sheets("报销统计综合查询")
- .Unprotect ("695360052")
- .Protect ("695360052")
- End With
- StrConn = "Provider= Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & AccessFile & ";"""
- Set AdoConn = CreateObject("ADODB.Connection")
- With AdoConn
- .CursorLocation = 3 '游标类型
- .CommandTimeout = 5 '超时
- .connectionTimeout = 5 '超时
- .Open StrConn '打开
- End With
- Set AdoRst = AdoConn.Execute(strSql)
- If AdoRst.RecordCount = 0 Then
- MsgBox "无合乎条件的数据"
- Exit Sub
- Else
- Application.ScreenUpdating = False
- Range("a6").CopyFromRecordset AdoRst
- End If
- AdoConn.Close
- Set AdoConn = Nothing
- Application.ScreenUpdating = True
- MsgBox "查询完成"
- Exit Sub
- Errcheck:
- MsgBox Err.Number & vbNewLine & _
- Err.Description
- End Sub
复制代码 |
|