【個稅計算】【根據(jù)模板生成excel表格】【記錄的查詢和篩選講解】
個稅計算
Private Sub Command計算_Click()
Dim income As Currency
Dim taxrate As Single
Dim deduct As Currency
If Me.稅前收入 <> "" Then
? ? If IsNumeric(Me.稅前收入) = False Then
? ? ? ? MsgBox "請輸入數(shù)值類型數(shù)據(jù)"
? ? ? ? Exit Sub
? ? End If
Else
MsgBox "請輸入收入"
Exit Sub
End If
If CCur(Me.稅前收入) <= 0 Then
MsgBox "收入必須大于0"
Exit Sub
End If
income = Me.稅前收入
Me.稅后收入 = ""
Me.所得稅 = ""
Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("稅率表", dbOpenTable)
Do While rs1.EOF = False
If income > rs1!最低金額 And income <= rs1!最高金額.Value Then
? ? taxrate = rs1!稅率
? ? deduct = rs1!速算扣除數(shù)
? ? Me.所得稅 = Round(income * taxrate - deduct, 2)
? ? Me.稅后收入 = Me.稅前收入 - Me.所得稅
End If
rs1.MoveNext
Loop
End Sub
根據(jù)模板生成excel表格
生產(chǎn)表格
Private Sub Command生成表格_Click()
On Error GoTo outputerror
Dim copyfilename As String
copyfilename = "工資單模板.xlsx"? ? '要復(fù)制的源文件（Excel）
'輸入文件名
Dim outputname As String
outputname = InputBox("請輸入導(dǎo)出的文件名", "導(dǎo)出表", "工資單：" & Me.員工號 & Me.姓名 & Me.年份 & "年" & Me.月份 & "月") '輸入要生成的表名-
If outputname = "" Or IsNull(outputname) Then? ?'為空則不執(zhí)行程序
Exit Sub
End If
'選擇導(dǎo)出的位置（文件夾）
Dim exportpath As String
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
With dlgOpen
? ? ?If .Show = -1 Then
? ? ? ? exportpath = .SelectedItems(1)
? ? ? ? Else
? ? ? ? Exit Sub
? ? ?End If
End With
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
? ? fs.copyfile fs.BuildPath(CurrentProject.Path, copyfilename), exportpath & "\" & outputname & ".xlsx"
'打開創(chuàng)建的表并處理數(shù)據(jù)
Dim excelopenpathname As String
excelopenpathname = exportpath & "\" & outputname & ".xlsx"? ? ?'復(fù)制粘貼新Excel文件的路徑和名稱
'處理打開的表數(shù)據(jù)
Dim xlapp As Excel.Application
Dim xlwbk As Excel.Workbook
Dim xlwsh As Excel.Worksheet
Set xlapp = New Excel.Application
Dim CreateExcel As Object
Set CreateExcel = xlapp
xlapp.Visible = True
xlapp.ScreenUpdating = False
Set xlwbk = xlapp.Workbooks.Open(excelopenpathname)
xlwbk.Activate
Set xlwsh = xlwbk.Worksheets(1)
With xlwsh
? ? .Cells(4, "B") = Me.員工號
? ? .Cells(4, "C") = Me.姓名
? ? .Cells(4, "D") = Me.工資總額
? ? .Cells(4, "E") = Me.公積金
? ? .Cells(4, "F") = Me.養(yǎng)老保險
? ? .Cells(4, "G") = Me.失業(yè)保險
? ? .Cells(4, "H") = Me.醫(yī)療保險
? ? .Cells(4, "I") = Me.所得稅
? ? .Cells(4, "J") = Me.實發(fā)工資
? ??
End With
xlwbk.Save
xlwbk.Close
xlapp.ScreenUpdating = True
xlapp.Quit
MsgBox "生成完成"
'------------------------------------------
Exit Sub
outputerror:
MsgBox Err.Description
End Sub
記錄的查詢和篩選講解
導(dǎo)航
Private Sub Command打開_Click()
DoCmd.OpenForm "單記錄綁定窗體查詢", acNormal, , "所在部門='" & Me.Text部門 & "'"
End Sub
Private Sub Command打開列表_Click()
DoCmd.OpenForm "員工列表", acNormal, , "所在部門='" & Me.所在部門 & "'"
End Sub
Private Sub Command數(shù)據(jù)表_Click()
DoCmd.OpenForm "員工數(shù)據(jù)表", acFormDS, , "所在部門='" & Me.所在部門 & "'"
End Sub
多條件篩選查詢
Private Sub Command查詢_Click()
DoCmd.OpenQuery "員工條件查詢", acViewNormal
End Sub
Private Sub Command查詢2_Click()
DoCmd.OpenForm "員工條件查詢數(shù)據(jù)表", acFormDS
End Sub
多條件篩選查詢2
Public filter_text As String? ? '定義為公共變量
Private Sub Command查詢_Click()
filter_text = ""
If Me.員工號 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 員工號 like '*" & Me.員工號 & "*'"
? ? Else
? ? filter_text = "員工號 like '*" & Me.員工號 & "*'"
? ? End If
End If
If Me.姓名 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 姓名 like '*" & Me.姓名 & "*'"
? ? Else
? ? filter_text = "姓名 like '*" & Me.姓名 & "*'"
? ? End If
End If
If Me.所在部門 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 所在部門 = '" & Me.所在部門 & "'"
? ? Else
? ? filter_text = "所在部門 = '" & Me.所在部門 & "'"
? ? End If
End If
If Me.工齡 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工齡 >=" & Me.工齡
? ? Else
? ? filter_text = "工齡 >=" & Me.工齡
? ? End If
End If
If Me.出生日期1 <> "" And Me.出生日期2 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? Else
? ? filter_text = "工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? End If
End If
'------------------------------篩選子窗體
If filter_text <> "" Then
DoCmd.OpenForm "員工列表", acNormal, , filter_text
Else
DoCmd.OpenForm "員工列表", acNormal
End If
End Sub
列表框篩選
Private Sub Command部門_Click()
Me.員工查詢列表.RowSource = "Select 員工號,姓名,性別,所在部門,職位,工齡 From 員工表 Where 所在部門 like '*" & InputBox("請輸入所在部門", "按部門查詢") & "*'"
End Sub
Private Sub Command姓名_Click()
Me.員工查詢列表.RowSource = "Select 員工號,姓名,性別,所在部門,職位,工齡 From 員工表 Where 姓名 like '*" & InputBox("請輸入姓名", "按姓名查詢") & "*'"
End Sub
Private Sub Command員工號_Click()
Me.員工查詢列表.RowSource = "Select 員工號,姓名,性別,所在部門,職位,工齡 From 員工表 Where 員工號 = '" & InputBox("請輸入員工號", "按員工號查詢") & "'"
End Sub
Private Sub Command職位_Click()
Me.員工查詢列表.RowSource = "Select 員工號,姓名,性別,所在部門,職位,工齡 From 員工表 Where 職位 like '*" & InputBox("請輸入現(xiàn)聘職務(wù)", "按現(xiàn)聘職務(wù)查詢") & "*'"
End Sub
員工列表多條件查詢
Public filter_text As String? ? '定義為公共變量
Private Sub Command查詢_Click()
filter_text = ""
If Me.員工號查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 員工號 like '*" & Me.員工號查詢 & "*'"
? ? Else
? ? filter_text = "員工號 like '*" & Me.員工號查詢 & "*'"
? ? End If
End If
If Me.姓名查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 姓名 like '*" & Me.姓名查詢 & "*'"
? ? Else
? ? filter_text = "姓名 like '*" & Me.姓名查詢 & "*'"
? ? End If
End If
If Me.所在部門查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 所在部門 = '" & Me.所在部門查詢 & "'"
? ? Else
? ? filter_text = "所在部門 = '" & Me.所在部門查詢 & "'"
? ? End If
End If
If Me.工齡查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工齡 >=" & Me.工齡查詢
? ? Else
? ? filter_text = "工齡 >=" & Me.工齡查詢
? ? End If
End If
If Me.出生日期1 <> "" And Me.出生日期2 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? Else
? ? filter_text = "工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? End If
End If
'------------------------------篩選
If filter_text <> "" Then
Me.Filter = filter_text
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
Private Sub Command全部_Click()
Me.FilterOn = False
End Sub
員工篩選子窗體
Public filter_text As String? ? '定義為公共變量
Private Sub Command查詢_Click()
filter_text = ""
If Me.員工號查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 員工號 like '*" & Me.員工號查詢 & "*'"
? ? Else
? ? filter_text = "員工號 like '*" & Me.員工號查詢 & "*'"
? ? End If
End If
If Me.姓名查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 姓名 like '*" & Me.姓名查詢 & "*'"
? ? Else
? ? filter_text = "姓名 like '*" & Me.姓名查詢 & "*'"
? ? End If
End If
If Me.所在部門查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 所在部門 = '" & Me.所在部門查詢 & "'"
? ? Else
? ? filter_text = "所在部門 = '" & Me.所在部門查詢 & "'"
? ? End If
End If
If Me.工齡查詢 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工齡 >=" & Me.工齡查詢
? ? Else
? ? filter_text = "工齡 >=" & Me.工齡查詢
? ? End If
End If
If Me.出生日期1 <> "" And Me.出生日期2 <> "" Then
? ? If filter_text <> "" Then
? ? filter_text = filter_text & " and 工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? Else
? ? filter_text = "工作時間 between #" & Me.出生日期1 & "# and #" & Me.出生日期2 & "#"
? ? End If
End If
'------------------------------篩選子窗體
If filter_text <> "" Then
Me.員工數(shù)據(jù)表.Form.Filter = filter_text
Me.員工數(shù)據(jù)表.Form.FilterOn = True
Else
Me.員工數(shù)據(jù)表.Form.FilterOn = False
End If
End Sub
Private Sub Command全部_Click()
Me.員工數(shù)據(jù)表.Form.FilterOn = False
End Sub
