VBA - 具有可选联接的SQL [英] VBA - SQL with optional joins

问题描述

我有一个组合者，我可以在其中选择多个值，但如果我将它们全部留空，我想从查询中排除该部分。这是我使用的当前逻辑

 如果ModeCat_ID =然后
设置rs = conn.Execute SELECT * FROM [ModeCat_T]）
 Else 
设置rs = conn.Execute（SELECT * FROM [ModeCat_T] WHERE [ModeCat_ID] ='& ModeCat_ID&'）
结束如果
  

我的问题是，这是最好的方法吗？我正在构建一个有更多选项的表单，所以我宁可不用一堆测试的Ifs来检查这些表单控件。

第二个问题是，如果它是一个可以选择多个的组合框..我将如何设置一个SQL IN语句？即

  IN（'1'，'2'，'3'）
  

谢谢！

编辑

 设置rs = conn.Execute（SELECT * FROM [ModeCat_T]）
  

解决方案

这是我通常如何做的：

  Dim strSQL As String 
 
 strSQL =set nocount on;
 strSQL = strSQL& select *
 strSQL = strSQL& from [ModeCat_T]
 strSQL = strSQL& 其中1 = 1
'***************************************** **************** 
'*根据需要添加其他限制
'**************** *************************************** 
如果修剪（Sheet1.Range（ B7）。Value2）< vbNullString然后
 strSQL = strSQL& 和[ModeCat_ID] ='& Sheet1.Range（B7）。Value2& '
 End If 
如果修剪（Sheet1.Range（B8）。Value2）<> vbNullString然后
 strSQL = strSQL& 和[ModeCat_Color] ='& Sheet1.Range（B8）。Value2& '
 End If 
如果修剪（Sheet1.Range（B9）。Value2）<> vbNullString然后
 strSQL = strSQL& 和[ModeCat_Size] ='& Sheet1.Range（B9）。Value2& '
 End If 
 
设置rs = conn.Execute（strSQL）
  

继续讨论过滤器，您可以从 ListBox 中选择许多不同的项目，以下代码可能可以帮助您： p>

  Dim strFilter As String 
 Dim lngItem As Long 
 
 For lngItem = 0 To UserForm1.ListBox1 .ListCount  -  1 
如果UserForm1.ListBox1.Selected（lngItem）= True Then 
如果strFilter<> vbNullString然后strFilter = strFilter& ，
 strFilter = strFilter& '
 strFilter = strFilter& UserForm1.ListBox1.List（lngItem，1）
 strFilter = strFilter& '
 End If 
 Next lngItem 
 
如果strFilter<> vbNullString然后
 strSQL = strSQL& 和（& strFilter&）中的[ModeCat_Form]
如果
  

基本上，它正在检查列表中的哪些项目已经被选中，并将它们放入一个字符串中（用逗号分隔，并以逗号分隔）。之后，代码检查是否有任何东西进入过滤器字符串。如果过滤器不为空，那么应该使用它。 BTW：如果字符串包含一个或多个项目，则无关紧要。所以，这也是允许的：

 和（'Oval'）中的[ModeCat_Shape] 
  

I have a combolist where I can select multiple values, but if I leave them all blank then I want to exclude that portion from the query. Here is the current logic I'm using

        If ModeCat_ID = "" Then
            Set rs = conn.Execute("SELECT * FROM [ModeCat_T]")
        Else
             Set rs = conn.Execute("SELECT * FROM [ModeCat_T] WHERE [ModeCat_ID] = '" & ModeCat_ID & "'")
        End If

My question is, is this the best way to go about this? I'm building a form that has a few more options so I'd rather not have a bunch of tested Ifs to check these form controls.

Second question is, if it's a combo box that can select multiples.. how would I set that up for an SQL IN statement? ie

IN ('1','2','3')

Thanks!

EDIT

 Set rs = conn.Execute("SELECT * FROM [ModeCat_T]")

解决方案

This is how I normally do it:

Dim strSQL As String

strSQL = "set nocount on; "
strSQL = strSQL & "select * "
strSQL = strSQL & "from [ModeCat_T] "
strSQL = strSQL & "     where 1 = 1  "
'*******************************************************
'*  Adding the other where restrictions as necessary
'*******************************************************
If Trim(Sheet1.Range("B7").Value2) <> vbNullString Then
    strSQL = strSQL & "     and [ModeCat_ID] = '" & Sheet1.Range("B7").Value2 & "' "
End If
If Trim(Sheet1.Range("B8").Value2) <> vbNullString Then
    strSQL = strSQL & "     and [ModeCat_Color] = '" & Sheet1.Range("B8").Value2 & "' "
End If
If Trim(Sheet1.Range("B9").Value2) <> vbNullString Then
    strSQL = strSQL & "     and [ModeCat_Size] = '" & Sheet1.Range("B9").Value2 & "' "
End If

Set rs = conn.Execute(strSQL)

Following the continued discussion about "Filters" where you can select possibly many different items from a ListBox the following code might be able to help with that:

Dim strFilter As String
Dim lngItem As Long

For lngItem = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(lngItem) = True Then
        If strFilter <> vbNullString Then strFilter = strFilter & ", "
        strFilter = strFilter & "'"
        strFilter = strFilter & UserForm1.ListBox1.List(lngItem, 1)
        strFilter = strFilter & "'"
    End If
Next lngItem

If strFilter <> vbNullString Then
    strSQL = strSQL & "and [ModeCat_Form] in (" & strFilter & ") "
End If

Basically, it is checking which items from the list have been selected and puts them into a string (wrapped in ' and separated by a comma). Afterwards the code checks if anything made it into the "Filter String". If the filter is not empty then it should be used. BTW: it does not matter if the string contains one or many items. So, this is also permissible:

   AND [ModeCat_Shape] in ('Oval')

这篇关于VBA - 具有可选联接的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！