我想根据以下几个条件查询得到查询结果:公司名称(文本框)、
所在洲(下拉框)、所在省(下拉框)、行业类型(下拉框)
我的查询语句为:
Dim strGjz As String
strGjz = "%" + txtGjz.Text + "%"
Dim strSzz As String
strSzz = "%" + cmbSzz_c.Text + "%"
Dim strSzgj As String
strSzgj = "%" + cmbSzgj.Text + "%"
Dim strSzs As String
strSzs = "%" + cmbSzs.Text + "%"
Dim strJyms As String
strJyms = "%" + cmbJyms_c.Text + "%"
Dim strQylx As String
strQylx = "%" + cmbQylx_c.Text + "%"
Dim strHylx As String
strHylx = "%" + cmbHylx_c.Text + "%"
Dim strGsdz As String
strGsdz = "%" + txtGsdz.Text + "%"
strQuery = "select * from gscxxt where 公司名称 like '" + strGjz + "' and 所在洲 like '" + strSzz + "' and 所在国家 like '" + strSzgj + "' And 所在省份 like '" + strSzs + "' and 经营模式 like '" + strJyms + "' and 企业类型 like'" + strQylx + "' and 行业类型 like '" + strHylx + "' and 公司地址 like '" + strGsdz + "'"
但是如果几个下拉框中的值任意为空的话,相应的记录就查不出来,我用的是acess数据库,是否在这里字段属性有什么需要设置的?我困惑了很久,希望智者帮我解解惑,谢谢!
所在洲(下拉框)、所在省(下拉框)、行业类型(下拉框)
我的查询语句为:
Dim strGjz As String
strGjz = "%" + txtGjz.Text + "%"
Dim strSzz As String
strSzz = "%" + cmbSzz_c.Text + "%"
Dim strSzgj As String
strSzgj = "%" + cmbSzgj.Text + "%"
Dim strSzs As String
strSzs = "%" + cmbSzs.Text + "%"
Dim strJyms As String
strJyms = "%" + cmbJyms_c.Text + "%"
Dim strQylx As String
strQylx = "%" + cmbQylx_c.Text + "%"
Dim strHylx As String
strHylx = "%" + cmbHylx_c.Text + "%"
Dim strGsdz As String
strGsdz = "%" + txtGsdz.Text + "%"
strQuery = "select * from gscxxt where 公司名称 like '" + strGjz + "' and 所在洲 like '" + strSzz + "' and 所在国家 like '" + strSzgj + "' And 所在省份 like '" + strSzs + "' and 经营模式 like '" + strJyms + "' and 企业类型 like'" + strQylx + "' and 行业类型 like '" + strHylx + "' and 公司地址 like '" + strGsdz + "'"
但是如果几个下拉框中的值任意为空的话,相应的记录就查不出来,我用的是acess数据库,是否在这里字段属性有什么需要设置的?我困惑了很久,希望智者帮我解解惑,谢谢!
解决方案 »
- vb 检测程序是否报错
- 窗体大小问题
- 不时接到询问 Grid++Report 使用许可的问题:在中国大陆地区是免费构件,在此作简要说明
- frmSplash不能卸载,实时错误 ‘365’
- 如何在VB中将窗体上的BarCodeCtrl1条形码图传到datareport报表中打印出来???
- datagrid控件显示数据的问题,大虾快帮忙
- 在控件内部,如何得到Usercontrol的外观大小参数?
- 网上与各位交个朋友,UP者有分
- '怎样把QQ在任务栏右下角的图标给换掉,或者控制隐藏/显示。
- Directx7.0
- 我用setup factory 6.0打包软件,到客户哪里安装时,在不同电脑安装,有时提示缺这个OCX文件,那个OCX文件,我怎么知道都要写什么OCX文件一起
- 如何通过vbAPI来选择listbox中的item?
If Trim(txtGjz.Text) <> "" Then
strString = strString & " AND 公司名称 like '%" & txtGjz.Text & "%'"
End If
If Trim(cmbSzz_c.Text) <> "" Then
strString = strString & " AND 所在洲 like '%" & cmbSzz_c.Text & "%'"
End If
If Trim(cmbSzgj.Text) <> "" Then
strString = strString & " AND 所在国家 like '%" & cmbSzgj.Text & "%'"
End If
If Trim(cmbSzs.Text) <> "" Then
strString = strString & " AND 所在省份 like '%" & cmbSzs.Text & "%'"
End If
If Trim(cmbJyms_c.Text) <> "" Then
strString = strString & " AND 经营模式 like '%" & cmbJyms_c.Text & "%'"
End If
If Trim(cmbQylx_c.Text) <> "" Then
strString = strString & " AND 企业类型 like '%" & cmbQylx_c.Text & "%'"
End If
If Trim(cmbHylx_c.Text) <> "" Then
strString = strString & " AND 行业类型 like '%" & cmbHylx_c.Text & "%'"
End If
If Trim(txtGsdz.Text) <> "" Then
strString = strString & " AND 公司地址 like '%" & txtGsdz.Text & "%'"
End If
strQuery = "select * from gscxxt where 1=1 " & strString
^_^
If Trim(txtGjz.Text) <> "" Then
strString = strString & " AND 公司名称 like '%" & txtGjz.Text & "%'"
End If
If Trim(cmbSzz_c.Text) <> "" Then
strString = strString & " AND 所在洲 like '%" & cmbSzz_c.Text & "%'"
End If
If Trim(cmbSzgj.Text) <> "" Then
strString = strString & " AND 所在国家 like '%" & cmbSzgj.Text & "%'"
End If
If Trim(cmbSzs.Text) <> "" Then
strString = strString & " AND 所在省份 like '%" & cmbSzs.Text & "%'"
End If
If Trim(cmbJyms_c.Text) <> "" Then
strString = strString & " AND 经营模式 like '%" & cmbJyms_c.Text & "%'"
End If
If Trim(cmbQylx_c.Text) <> "" Then
strString = strString & " AND 企业类型 like '%" & cmbQylx_c.Text & "%'"
End If
If Trim(cmbHylx_c.Text) <> "" Then
strString = strString & " AND 行业类型 like '%" & cmbHylx_c.Text & "%'"
End If
If Trim(txtGsdz.Text) <> "" Then
strString = strString & " AND 公司地址 like '%" & txtGsdz.Text & "%'"
End If
strQuery = "select * from gscxxt where 1=1 " & strString没错,,先让它判断下,,,再去执行SQL语句..
下面的代码可实现:根据你提供的任意几个条件文本框或列表框的内容(内容为空也可以)组合出一条查询语句:Private Sub Command1_Click()
Dim first As Boolean '首个条件标志
first = True
'公司名称子句
Dim strGjz As String
If Len(txtGjz.Text) <> 0 Then
If first = True Then
strGjz = " where 公司名称 like '%" + txtGjz.Text + "%' "
first = False
Else
strGjz = " and 公司名称 like '%" + txtGjz.Text + "%' "
End If
Else
strGjz = ""
End If
'所在洲子句
Dim strSzz As String
If Len(cmbSzz_c.Text) <> 0 Then
If first = True Then
strSzz = " where 所在洲 like '%" + cmbSzz_c.Text + "%' "
first = False
Else
strSzz = " and 所在洲 like '%" + cmbSzz_c.Text + "%' "
End If
Else
strSzz = ""
End If
'所在国家子句
Dim strSzgj As String
If Len(cmbSzgj.Text) <> 0 Then
If first = True Then
strSzgj = " where 所在国家 like '%" + cmbSzgj.Text + "%' "
first = False
Else
strSzgj = " and 所在国家 like '%" + cmbSzgj.Text + "%' "
End If
Else
strSzgj = ""
End If
'所在省份子句
Dim strSzs As String
If Len(cmbSzs.Text) <> 0 Then
If first = True Then
strSzs = " where 所在省份 like '%" + cmbSzs.Text + "%' "
first = False
Else
strSzs = " and 所在省份 like '%" + cmbSzs.Text + "%' "
End If
Else
strSzs = ""
End If
'经营模式子句
Dim strJyms As String
If Len(cmbJyms_c.Text) <> 0 Then
If first = True Then
strJyms = " where 经营模式 like '%" + cmbJyms_c.Text + "%' "
first = False
Else
strJyms = " and 经营模式 like '%" + cmbJyms_c.Text + "%' "
End If
Else
strJyms = ""
End If
'企业类型子句
Dim strQylx As String
If Len(cmbQylx_c.Text) <> 0 Then
If first = True Then
strQylx = " where 企业类型 like '%" + cmbQylx_c.Text + "%' "
first = False
Else
strQylx = " and 企业类型 like '%" + cmbQylx_c.Text + "%' "
End If
Else
strQylx = ""
End If
'行业类型子句
Dim strHylx As String
If Len(cmbHylx_c.Text) <> 0 Then
If first = True Then
strHylx = " where 行业类型 like '%" + cmbHylx_c.Text + "%' "
first = False
Else
strHylx = " and 行业类型 like '%" + cmbHylx_c.Text + "%' "
End If
Else
strHylx = ""
End If
'公司地址子句
Dim strGsdz As String
If Len(txtGsdz.Text) <> 0 Then
If first = True Then
strGsdz = " where 公司地址 like '%" + txtGsdz.Text + "%' "
first = False
Else
strGsdz = " and 公司地址 like '%" + txtGsdz.Text + "%' "
End If
Else
strGsdz = ""
End If
'组合查询语句
Dim strquery As String
strquery = "select * from gscxxt " + strGjz + strSzz + strSzgj + strSzs + strJyms + strQylx + strHylx + strGsdz + " ;"
'调试检查
Debug.Print strquery
End Sub
没有进行测试,但思路是这样了
自己根据Debug.Print的结果看看吧。。
知道里面的什么了吧?^_^
dim tmp_szz as string
dim tmp_szs as string
dim tmp_hylx as string
dim StrSql as string
if trim(txtgsmc.text)="" then
tmp_gsmc=""
else
tmp_gsmc=" and 公司名称字段='"& trim(txtgsmc.text) &"'"
end if
if trim(cmbszz.text)="" then
tmp_szz=""
else
tmp_szz=" and 所在洲字段='"& trim(cmbszz.text) &"'"
end if
if trim(cmbszc.text)="" then
tmp_szs=""
else
tmp_szs=" and 所在省字段='"& trim(cmbszs.text) &"'"
end if
if trim(cmbhylx.text)="" then
tmp_hylx=""
else
tmp_hylx=" and 行业类型字段='"& trim(cmbhylx.text) &"'"
end if'构造SQL查询语句
strsql=" select * from tablename where 1=1 " & tmp_gsmc & tmp_szz & tmp_szs & tmp_hylx
知道里面的什么了吧?^_^
============================
呵呵小健兄取巧取得好
strQuery = "SELECT * FROM gscxxt WHERE " _
& IIf(Trim(txtGjz.Text) <> "", "公司名称 like '%" + Trim(txtGjz.Text) + "%'", "0=0") + " AND " _
& IIf(Trim(cmbSzz_c.Text) <> "" <> "", "所在洲 like '%" + Trim(cmbSzz_c.Text) <> "" + "%'", "0=0") + " AND " _
& IIf(Trim(cmbSzgj.Text) <> "", "所在国家 like '%" + Trim(cmbSzgj.Text) <> "" + "%'", "0=0") + " AND " _
& IIf(Trim(cmbSzs.Text) <> "", "所在省份 like '%" + Trim(cmbSzs.Text) <> "" + "%'", "0=0") + " AND " _
& IIf(Trim(cmbJyms_c.Text) <> "", "经营模式 like '%" + Trim(cmbJyms_c.Text) + "%'", "0=0") + " AND " _
& IIf(Trim(cmbQylx_c.Text) <> "", "企业类型 like '%" + Trim(cmbQylx_c.Text) + "%'", "0=0") + " AND " _
& IIf(Trim(cmbHylx_c.Text) <> "", "行业类型 like '%" + Trim(cmbHylx_c.Text) + "%'", "0=0") + " AND " _
& IIf(Trim(txtGsdz.Text) <> "", "公司地址 like '%" + Trim(txtGsdz.Text) + "%'", "0=0")