VBA窗体中多条件SQL查询时如何写代码？

自讨莓趣

   这种三个条件的判断空值的情况已经很多了，4个条件以上的会更多？如何写语句，当条件值为空时不参加查询呢？
Private Sub CommandButton1_Click()
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Dim Cnn As New ADODB.Connection
     Dim Rst As New ADODB.Recordset
     Dim strSQL As String
     Cnn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & stpath & ";Jet OLEDB:Database Password=" & "2345"
     If TextBox1.Value = "" And ComboBox1 = "" And ComboBox2 = "" Then
     strSQL = "Select * from 营业数据 "
     ElseIf TextBox1.Value = "" And ComboBox1 = "" And ComboBox2 <> "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.状态 =  '" & ComboBox2.Value & "' "
     ElseIf TextBox1.Value = "" And ComboBox1 <> "" And ComboBox2 = "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.区域 = '" & ComboBox1.Value & "' "
     ElseIf TextBox1.Value <> "" And ComboBox1 = "" And ComboBox2 = "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.合同号 = '" & TextBox1.Value & "' "
     ElseIf TextBox1.Value <> "" And ComboBox1 <> "" And ComboBox2 = "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.合同号 = '" & TextBox1.Value & "'AND 营业数据.区域 = '" & ComboBox1.Value & "' "
     ElseIf TextBox1.Value <> "" And ComboBox1 = "" And ComboBox2 <> "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.合同号 = '" & TextBox1.Value & "'AND 营业数据.状态 =  '" & ComboBox2.Value & "' "
     ElseIf TextBox1.Value = "" And ComboBox1 <> "" And ComboBox2 <> "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.区域 = '" & ComboBox1.Value & "'AND 营业数据.状态 =  '" & ComboBox2.Value & "' "
     ElseIf TextBox1.Value <> "" And ComboBox1 <> "" And ComboBox2 <> "" Then
     strSQL = "Select * from 营业数据 WHERE 营业数据.合同号 = '" & TextBox1.Value & "'AND 营业数据.区域 = '" & ComboBox1.Value & "'AND 营业数据.状态 =  '" & ComboBox2.Value & "' "
     End If
     Rst.Open strSQL, Cnn
     Sheet13.Range("A3:S20000").ClearContents
     Sheet13.Cells(3, 1).CopyFromRecordset Rst
     Sheet13.Cells.NumberFormatLocal = "G/通用格式"
     Sheet13.Range("R:R").NumberFormatLocal = "yyyy-m-d"
     Sheet13.Activate
    Rst.Close
    Set Rst = Nothing
    Set Cnn = Nothing
    Sheet13.Visible = True
    'Sheet13.Range("A1").Select

    Application.ScreenUpdating = True
    Application.GoTo Sheet13.Range("A3")
Application.Calculation = xlCalculationAutomatic
End Sub

chxw68

1. Private Sub CommandButton1_Click()
   
2.   ss = ""
   
3.   With Me
   
4.     If .TextBox1.Value <> "" Then
   
5.       ss = ss & " and 营业数据.合同号='" & .TextBox1.Value & "'"
   
6.     End If
   
7.     If .ComboBox1.Value <> "" Then
   
8.       ss = ss & " and 营业数据.区域='" & .ComboBox1.Value & "'"
   
9.     End If
   
10.     If .ComboBox2.Value <> "" Then
    
11.       ss = ss & " and 营业数据.状态='" & .ComboBox2.Value & "'"
    
12.     End If
    
13.     ss = Mid(ss, 5)
    
14.   End With
    
15.   strsql = "select * from 营业数据 where " & ss
    
16. End Sub
    

复制代码

自讨莓趣

chxw68 发表于 2014-12-23 10:36

非常感谢老师回复！

qinyu0745

留个记号，后面用得上

公子段

学习了，非常感谢老师们分享知识。

lsdongjh

chxw68 发表于 2014-12-23 10:36

这个方法有个大问题，当所有条件均为空时，ss为空，整个语句为

select * from 营业数据 where
会出错的！！

其实，楼主的问题，在业内有个小技巧的：

前置语句为：select * form table where 1=1