where语句个数可变时进行查询

Question

我在几年内没有使用访问，但我试图创建一个搜索表单，用户可以在8个不同的文本框中输入搜索条件，然后根据有多少框有数据进行搜索。而不是试图为每个组合创建一个sql语句，我已经尝试创建一个sql语句，将工作，但还没有找到任何东西。

我已经尝试使用'*'为每个空白文本框不工作。我使用每个列的变量，并认为我可以使用一个空字符串的空文本框列，但我不知道哪一个将是最后一个，以及如何使最后一个变量没有"one_answers"在它后面。我可以循环遍历这些变量，但有没有更简单的方法?

这是我现在的代码…

Private Sub Command3_Click()
Dim frm As Form, rs As Recordset
Dim nb As String, cs As String, s As String, ss As String
Dim od As String, cd As String, t As String, tt As String, st As String
Dim sql_select As String, sql_from As String, sql_where As String
If IsNull(Me.txtNumb) Then nb = "" Else nb = "nc.[NC Number] = """ & Me.txtNumb & """"
If IsNull(Me.txtCS) Then cs = "" Else cs = "nc.[CS_Build] = """ & Me.txtCS & """"
If IsNull(Me.cbSection) Then s = "" Else s = "nc.[Section] = """ & Me.cbSection & """"
If IsNull(Me.cbSubSection) Then ss = "" Else ss = "nc.[Sub-Section] = """ & Me.cbSubSection & """"
If IsNull(Me.txtOpenDate) Then od = "" Else od = "nc.[Date_Open] = """ & Me.txtOpenDate & """"
If IsNull(Me.txtClosedDate) Then cd = "" Else cd = "nc.[Date_Closed] = """ & Me.txtClosedDate & """"
If IsNull(Me.cbTPS) Then t = "" Else t = "nc.[TPS] = """ & Me.cbTPS & """"
If IsNull(Me.cbTPStype) Then tt = "" Else tt = "nc.[TPS_Type] = """ & Me.cbTPStype & """"
If IsNull(Me.cbStatus) Then st = "" Else st = "nc.[Status] = """ & Me.cbStatus & """"
sql_select = "SELECT nc.[NC Number], nc.[Date_Open], nc.[CS_Build], nc.[Section], nc.[Sub-Section], nc.[Status], nc.[Date_Closed], nc.[Notes] "
sql_from = sql_query + "FROM nc "
sql_where = sql_query + "WHERE " & nb & " And " & od & " And " & cs & " And " & s & " And " & st & " And " & cd & " And " & ss & " And " & t & " And " & tt & ";"
Me.lstSearch.RowSource = sql_select + sql_from + sql_where
End Sub

Answer 1

我想出了一个办法。我不确定这是一个好方法。我替换了空字符串"当文本框为空时，输入一些始终为真的内容，如"1=1"这允许执行查询。

但是有更好的方法吗?