access数据库查询字段知识点（绑定记录多种类型查询和筛选功能讲解-条件查询）

导航窗体PrivateSubCommand打开_Click()DoCmd.OpenForm"单记录绑定窗体查询",acNormal,,"所在部门='"&Me.Text部门&"'"EndSubPrivateSubCommand打开列表_Click()DoCmd.OpenForm"员工列表",acNormal,,"所在部门='"&Me.所在部门&"'"EndSubPrivateSubCommand数据

Private Sub Command打开_Click()

DoCmd.OpenForm "单记录绑定窗体查询", acNormal, , "所在部门='" & Me.Text部门 & "'"

End Sub

Private Sub Command打开列表_Click()

DoCmd.OpenForm "员工列表", acNormal, , "所在部门='" & Me.所在部门 & "'"

End Sub

Private Sub Command数据表_Click()

DoCmd.OpenForm "员工数据表", acFormDS, , "所在部门='" & Me.所在部门 & "'"

End Sub

Private Sub Command查询_Click()

DoCmd.OpenQuery "员工条件查询", acViewNormal

End Sub

Private Sub Command查询2_Click()

DoCmd.OpenForm "员工条件查询数据表", acFormDS

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

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("请输入现聘职务", "按现聘职务查询") & "*'"

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.员工数据表.Form.Filter = filter_text

Me.员工数据表.Form.FilterOn = True

Else

Me.员工数据表.Form.FilterOn = False

End If

End Sub

Private Sub Command全部_Click()

Me.员工数据表.Form.FilterOn = False

End Sub