sql - VBA - 创建基于条件的 SQL 查询
问题描述
我对 VBA 很陌生,我想找到一种更简单的方法来创建 SQL 查询。我有 4 个组合框,查询取决于它们是否被填充。其中一个组合框链接到带有多个复选框的用户表单,这些复选框将添加到查询中。
这是我目前拥有的代码,我想增加用户表单条件:
If Sheets("Home Page").opecbbx.Value <> "" And Sheets("Home Page").wellchk.Value = False Then 'contractor only
op = Sheets("Home Page").opecbbx.Value
If Sheets("Home Page").sizcbbx.Value <> "" Then 'contractor and size
'Create SQL statement for filtering.
siz = Sheets("Home Page").sizcbbx.Value
SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' And size_sec Like '%" & siz & "%';"
Else
SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%';"
End If
ElseIf Sheets("Home Page").wellchk.Value = True And Sheets("Home Page").opecbbx.Value <> "" Then 'contractor and well
op = Sheets("Home Page").opecbbx.Value
If Sheets("Home Page").sizcbbx.Value <> "" Then 'contractor, size and well
siz = Sheets("Home Page").sizcbbx.Value
SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' And size_sec Like '%" & siz & "%' And ID_NOC = '" & well & "';"
Else
SQL = "SELECT * FROM Filter Where contractor Like '%" & op & "%' and ID_NOC='" & well & "';"
End If
ElseIf Sheets("Home Page").wellchk.Value = True Then
If Sheets("Home Page").sizcbbx.Value <> "" Then 'size and well
siz = Sheets("Home Page").sizcbbx.Value
SQL = "SELECT * FROM Filter Where size_sec Like '%" & siz & "%' And ID_NOC = '" & well & "';"
Else
SQL = "SELECT * FROM Filter Where ID_NOC='" & well & "';"
End If
ElseIf Sheets("Home Page").sizcbbx.Value <> "" And Sheets("Home Page").wellchk.Value = False And Sheets("Home Page").opecbbx.Value = "" Then 'size only
siz = Sheets("Home Page").sizcbbx.Value
SQL = "SELECT * FROM Filter Where size_sec Like '%" & siz & "%';"
'Formation only
ElseIf Sheets("Home Page").wellchk.Value = False And Sheets("Home Page").opecbbx.Value = "" And Sheets("Home Page").sizcbbx.Value = "" Then
SQL = "SELECT * FROM Filter"
End If
在不添加大量新条件的情况下如何做到这一点?
谢谢
解决方案
我可以在这里给出一个一般性的答案,这可能需要一些按摩才能在 VBA 中工作。您可以使用如下灵活的准备好的语句:
SELECT *
FROM Filter
WHERE
(contractor LIKE ? OR contractor IS NULL) AND
(size_sec LIKE ? OR size_sec IS NULL) AND
(ID_NOC = ? OR ID_NOC IS NULL);
对于每个占位符?
,您可以绑定组合框中的值(如果已定义),或者仅绑定NULL
。例如,对于contractor
占位符,如果已定义,您将绑定op
,否则NULL
。这种方法的工作方式是,如果给定的标准是NULL
,那么它就会被忽略。因此,如果您只有组合框中的三个可用值之一,那么只有它会用于在WHERE
子句中进行过滤。
推荐阅读
- mysql - MySQL 5.6 won't launch because it can't write to a world writeable directory
- ios - 调用 array.removeAll() 后 tableview.reload 不起作用 swift 5
- c# - How to find the date time of the most recent file
- java - 仅 GLSL 统一结构数组定义第一个元素
- java - cover text inside an image with firebase
- java - Why is DownloadManager.Request "allowScanningByMediaScanner()" not working on Android Q?
- flask - Flask create_app with celery workers dropping when getting tasks
- docker - Dockerfile,如何安装 snap,snapd:无法识别的服务
- node.js - nodejs kubernetes无法执行滚动更新
- python - Simplify two numpy arrays by removing matching element sums