如何使用VBA在MS Access中使用多字段筛选表单

I have the code below in a click button event in MS Access 2013 form, whose datasource is from a query with the following fields “EnrNo, FirstName, LastName, and RegDate”.

我在MS Access 2013表单中的单击按钮事件中有以下代码,其数据源来自具有以下字段“EnrNo,FirstName,LastName和RegDate”的查询。

The form has three text boxes and a command button:

表单有三个文本框和一个命令按钮:

txtKeyword
txtDateFrom
txtDateTo
cmdSearch

The click button (cmdSearch) in the form is intended to filter the query based on three criteria’s , which could be any of the two “EnrNo, FirstName, LastName” AND the range of the date(s) entered in the text box txtDateTo and cmdSearch

表单中的单击按钮(cmdSearch)旨在根据三个条件筛选查询,这三个条件可以是两个“EnrNo,FirstName,LastName”中的任意一个以及在文本框txtDateTo中输入的日期范围和cmdSearch

My code successfully filters only EnrNo. Please help me out... Thanks for your time.

我的代码只成功过滤了EnrNo。请帮帮我...谢谢你的时间。

Private Sub cmdSearch_Click()
Dim strWhere As String                  'The criteria string.
Dim Where As String
Dim lngLen As Long                      'Length of the criteria string   to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilter) Then
strWhere = strWhere & "([EnrNo] = """ & Me.txtFilter & """) OR "
strWhere = strWhere & "([FirstName] = """ & Me.txtFilter & """) AND "
End If

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtFrom) Then
strWhere = strWhere & "([RegDate] >= " & Format(Me.txtFrom, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtTo) Then   'Less than the next day.
strWhere = strWhere & "[RegDate] BETWEEN #" & Format(Me.txtFrom, "mm/dd/yyyy") & "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "# "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then     'Nah: there was nothing in the string.
    MsgBox "No criteria", vbInformation, "Nothing to do."
Else                    'Yep: there is something there, so remove the "  AND " at the end.
    strWhere = Left$(strWhere, lngLen)
  'Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

1 个解决方案

#1

It displays “Run-time error ‘3075’: Syntax error in data in query expression ‘([EnrNo] = “MS-12/IT-004”) or ([FirstName] = “MS-12/IT-004”) AND ([RegDate] >= #06/16/2014#) AND [RegDate] BETWEEN #06/16/2014# AND #09/23/2’ you have too many operands here.

它显示“运行时错误'3075':查询表达式中的数据中的语法错误'([EnrNo] =”MS-12 / IT-004“)或([FirstName] =”MS-12 / IT-004“) AND([RegDate]> =#06/16/2014#)和[RegDate] BETWEEN#06/16/2014#AND#09/23/2'你这里有太多的操作数。

You will need to make it so your query's where statement is as follows;

你需要这样做,所以你的查询的where语句如下;

(([EnrNo] = “MS-12/IT-004”) or ([FirstName] = “MS-12/IT-004”)) AND (([RegDate] >= #06/16/2014#) AND ([RegDate] BETWEEN #06/16/2014# AND #09/23/2014#))

Note the extra brackets I have placed in there. Logical operators (OR/AND) will return a value based upon TWO expressions. So for instance, a > b AND b > c returns true if BOTH conditions are true. a > b OR b > c returns true if EITHER statement is true.

请注意我放在那里的额外括号。逻辑运算符(OR / AND)将返回基于TWO表达式的值。因此,例如,如果BOTH条件为真,则a> b AND b> c返回true。 a> b OR b> c如果EITHER语句为真,则返回true。

If we write a statement like a > b and b > c or d > f we have too many conditions, so we need to nest them. So we would write it as (a > b AND b > c) OR d > f.

如果我们写一个像a> b和b> c或d> f的语句,我们有太多的条件,所以我们需要嵌套它们。所以我们把它写成(a> b AND b> c)或d> f。

A good way to practice this is to create the query with the criteria manually in the query builder, then look at the bracketing placed on your conditions in the SQL view window of the query.

练习此方法的一种好方法是在查询构建器中手动创建带有条件的查询,然后在查询的SQL视图窗口中查看条件中的括号。

I hope this helps!

我希望这有帮助!

#1