dim bWhere as boolean strsql="select * from table " if 需要条件A then strsql=strsql & " where 条件A " bWhere=true end if if 需要条件B then if bWhere=true then strsql=strsql & " and 条件B " else strsql=strsql & "where 条件B" bwhere=true end if end if ...
up!sql="select * from table where 字段1=值 and 字段2=值 and ......" 再执行
这是我以前做的,看看 Private longcount As Long Dim sname As String Dim srela As StringPrivate Sub Cmdadd_Click() If Combo1.Text = "文书编号" Then sname = "bh" ElseIf Combo1.Text = "文书名称" Then sname = "mc" ElseIf Combo1.Text = "文书年代" Then sname = "nd" ElseIf Combo1.Text = "出土时间" Then sname = "sj" ElseIf Combo1.Text = "所属出土地点" Then sname = "mzmx.mzmc" ElseIf Combo1.Text = "文书现状" Then sname = "xz" ElseIf Combo1.Text = "文书质地" Then sname = "lb" ElseIf Combo1.Text = "文书性质" Then sname = "lx" ElseIf Combo1.Text = "文字类型" Then sname = "wzlx" ElseIf Combo1.Text = "文书尺寸" Then sname = "cc" ElseIf Combo1.Text = "文书位置" Then sname = "wz" ElseIf Combo1.Text = "相关文献" Then sname = "wx" ElseIf Combo1.Text = "文书说明" Then sname = "yw" End If
If Trim(Txtvalue.Text = "") Then MsgBox "请输入值", vbOKOnly, "提示信息" Txtvalue.SetFocus Exit Sub End IfIf Combo2.Text = "等于" Then srela = "=" List1.AddItem sname & " " & srela & " " & "'" & Txtvalue.Text & "'" & " " & "and" & " " ElseIf Combo2.Text = "相似于" Then srela = "like" List1.AddItem sname & " " & srela & " " & "'%" & Txtvalue.Text & "%' " & " " & "and" & " " End If'List1.AddItem sname & " " & srela & " " & Txtvalue.Text & " " & "and" & " "End SubPrivate Sub Cmddel_Click() If List1.ListCount = 0 Then Exit Sub End If Dim I As Integer 'If List1.Selected(List1.ListIndex) Then ' List1.RemoveItem ((List1.ListIndex)) 'End If For I = 0 To List1.ListCount - 1 If I = List1.ListCount Then Exit For If List1.Selected(I) Then List1.RemoveItem I End If NextEnd SubPrivate Sub Command1_Click() Dim sql As String Dim sqlwhere As String For I = 0 To List1.ListCount - 1 sqlwhere = sqlwhere + List1.List(I) Next 'If Txtbh.Text = "" And Txtmc.Text = "" Then ' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "'" 'ElseIf Txtbh.Text = "" Then ' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and mc like '%" & Txtmc & "%'" 'ElseIf Txtmc.Text = "" Then ' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and bh like '%" & Txtbh & "%'" 'End If'If Txtbh.Text <> "" And Txtmc.Text <> "" Then ' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and mc like '%" & Txtmc & "%' and bh like '%" & Txtbh & "%'" 'End IfIf List1.ListCount = 0 Then sql = "select ws.id,bh,mc,nd,sj,mzmx.mzmc,xz,lb,lx,wzlx,cc,wz,wx,yw from mzmx,ws where mzmx.id=ws.mid" Else sql = "select ws.id,bh,mc,nd,sj,mzmx.mzmc,xz,lb,lx,wzlx,cc,wz,wx,yw from mzmx,ws where mzmx.id=ws.mid and " 'Dim nn As Long 'nn = Len(sql) 'nn = Len(Right(sql, 3)) 'nn = Len(sql) - Len(Right(sql, 3)) sql = sql + sqlwhere sql = Left(sql, Len(sql) - Len(Right(sql, 4)))End If'frmMain.SetFocus Call frmMain.Search(sql) End SubPrivate Sub AddCombo1() '添加字段选择 Combo1.AddItem "文书编号" Combo1.AddItem "文书名称" Combo1.AddItem "文书年代" Combo1.AddItem "出土时间" Combo1.AddItem "所属出土地点" Combo1.AddItem "文书现状" Combo1.AddItem "文书质地" Combo1.AddItem "文书性质" Combo1.AddItem "文字类型" Combo1.AddItem "文书尺寸" Combo1.AddItem "文书位置" Combo1.AddItem "相关文献" Combo1.AddItem "文书说明" Combo1.ListIndex = 0 End SubPrivate Sub AddCombo2() '添加字段选择 Combo2.AddItem "等于" Combo2.AddItem "相似于" Combo2.ListIndex = 0 End SubPrivate Sub Command4_Click() Unload Me End SubPrivate Sub Form_Load() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim strsql As String strsql = "select * from ws" rs.Open strsql, conn, adOpenForwardOnly, adLockReadOnlyrs.Close Set rs = Nothing Call SetFormFont(Me) Call AddCombo1 Call AddCombo2 End Sub
一个例子: Public Function M_Access(M_AccessName As String) Dim M_temp As String Dim M_Error As String On Error GoTo ine Set conn = New Connection Set rs = New ADODB.Recordset M_temp = "" M_temp = App.Path + "\" & M_AccessName & ";" conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & M_temp & ";" Set rs = conn.Execute("select * from login where number=1 and numText='12'") U_Setting.name = rs.Fields(0).value U_Setting.jname = rs.Fields(1).value U_Setting.adress = rs.Fields(2).value U_Setting.tel = rs.Fields(3).value U_Setting.fax = rs.Fields(4).value U_Setting.zip = rs.Fields(5).value U_Setting.lxr = rs.Fields(6).value U_Setting.Servername = rs.Fields(7).value U_Setting.Databasename = rs.Fields(8).value U_Setting.odbc = rs.Fields(9).value U_Setting.username = rs.Fields(11).value U_Setting.Password = rs.Fields(12).value rs.Close conn.Close GoTo inerr ine: M_Error = "数据库错误号:" & Err.Number & " 错误内容:" & Err.Description MsgBox M_Error, , "错误信息" inerr: Set rs = Nothing End Function
建议除非不得已,不要用那么多的elseif,不好.
那么多的elseif 可以用 select case Combo1.Text case "文书编号" sname = "bh" case "文书名称" sname = "mc" ...end select替换的。
基本上这样用就可以了: select 字段1,字段2... from 表名1,表名2... where 条件1 and 条件2 and ....
strsql="select * from table "
if 需要条件A then
strsql=strsql & " where 条件A "
bWhere=true
end if
if 需要条件B then
if bWhere=true then
strsql=strsql & " and 条件B "
else
strsql=strsql & "where 条件B"
bwhere=true
end if
end if
...
再执行
Private longcount As Long
Dim sname As String
Dim srela As StringPrivate Sub Cmdadd_Click()
If Combo1.Text = "文书编号" Then
sname = "bh"
ElseIf Combo1.Text = "文书名称" Then
sname = "mc"
ElseIf Combo1.Text = "文书年代" Then
sname = "nd"
ElseIf Combo1.Text = "出土时间" Then
sname = "sj"
ElseIf Combo1.Text = "所属出土地点" Then
sname = "mzmx.mzmc"
ElseIf Combo1.Text = "文书现状" Then
sname = "xz"
ElseIf Combo1.Text = "文书质地" Then
sname = "lb"
ElseIf Combo1.Text = "文书性质" Then
sname = "lx"
ElseIf Combo1.Text = "文字类型" Then
sname = "wzlx"
ElseIf Combo1.Text = "文书尺寸" Then
sname = "cc"
ElseIf Combo1.Text = "文书位置" Then
sname = "wz"
ElseIf Combo1.Text = "相关文献" Then
sname = "wx"
ElseIf Combo1.Text = "文书说明" Then
sname = "yw"
End If
If Trim(Txtvalue.Text = "") Then
MsgBox "请输入值", vbOKOnly, "提示信息"
Txtvalue.SetFocus
Exit Sub
End IfIf Combo2.Text = "等于" Then
srela = "="
List1.AddItem sname & " " & srela & " " & "'" & Txtvalue.Text & "'" & " " & "and" & " "
ElseIf Combo2.Text = "相似于" Then
srela = "like"
List1.AddItem sname & " " & srela & " " & "'%" & Txtvalue.Text & "%' " & " " & "and" & " "
End If'List1.AddItem sname & " " & srela & " " & Txtvalue.Text & " " & "and" & " "End SubPrivate Sub Cmddel_Click()
If List1.ListCount = 0 Then
Exit Sub
End If
Dim I As Integer
'If List1.Selected(List1.ListIndex) Then
' List1.RemoveItem ((List1.ListIndex))
'End If
For I = 0 To List1.ListCount - 1
If I = List1.ListCount Then Exit For
If List1.Selected(I) Then
List1.RemoveItem I
End If
NextEnd SubPrivate Sub Command1_Click()
Dim sql As String
Dim sqlwhere As String
For I = 0 To List1.ListCount - 1
sqlwhere = sqlwhere + List1.List(I)
Next
'If Txtbh.Text = "" And Txtmc.Text = "" Then
' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "'"
'ElseIf Txtbh.Text = "" Then
' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and mc like '%" & Txtmc & "%'"
'ElseIf Txtmc.Text = "" Then
' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and bh like '%" & Txtbh & "%'"
'End If'If Txtbh.Text <> "" And Txtmc.Text <> "" Then
' sql = "select bh,mc,nd,sj,mq,xz,lb,wz,wzlx from ws where mq='" & ImgCombomq.Text & "' and lb='" & ImgCombolx.Text & "' and wzlx='" & Combowzlx.Text & "' and mc like '%" & Txtmc & "%' and bh like '%" & Txtbh & "%'"
'End IfIf List1.ListCount = 0 Then
sql = "select ws.id,bh,mc,nd,sj,mzmx.mzmc,xz,lb,lx,wzlx,cc,wz,wx,yw from mzmx,ws where mzmx.id=ws.mid"
Else
sql = "select ws.id,bh,mc,nd,sj,mzmx.mzmc,xz,lb,lx,wzlx,cc,wz,wx,yw from mzmx,ws where mzmx.id=ws.mid and "
'Dim nn As Long
'nn = Len(sql)
'nn = Len(Right(sql, 3))
'nn = Len(sql) - Len(Right(sql, 3))
sql = sql + sqlwhere
sql = Left(sql, Len(sql) - Len(Right(sql, 4)))End If'frmMain.SetFocus
Call frmMain.Search(sql)
End SubPrivate Sub AddCombo1()
'添加字段选择
Combo1.AddItem "文书编号"
Combo1.AddItem "文书名称"
Combo1.AddItem "文书年代"
Combo1.AddItem "出土时间"
Combo1.AddItem "所属出土地点"
Combo1.AddItem "文书现状"
Combo1.AddItem "文书质地"
Combo1.AddItem "文书性质"
Combo1.AddItem "文字类型"
Combo1.AddItem "文书尺寸"
Combo1.AddItem "文书位置"
Combo1.AddItem "相关文献"
Combo1.AddItem "文书说明"
Combo1.ListIndex = 0
End SubPrivate Sub AddCombo2()
'添加字段选择
Combo2.AddItem "等于"
Combo2.AddItem "相似于"
Combo2.ListIndex = 0
End SubPrivate Sub Command4_Click()
Unload Me
End SubPrivate Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strsql As String
strsql = "select * from ws"
rs.Open strsql, conn, adOpenForwardOnly, adLockReadOnlyrs.Close
Set rs = Nothing
Call SetFormFont(Me)
Call AddCombo1
Call AddCombo2
End Sub
Public Function M_Access(M_AccessName As String)
Dim M_temp As String
Dim M_Error As String
On Error GoTo ine
Set conn = New Connection
Set rs = New ADODB.Recordset
M_temp = ""
M_temp = App.Path + "\" & M_AccessName & ";"
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & M_temp & ";"
Set rs = conn.Execute("select * from login where number=1 and numText='12'")
U_Setting.name = rs.Fields(0).value
U_Setting.jname = rs.Fields(1).value
U_Setting.adress = rs.Fields(2).value
U_Setting.tel = rs.Fields(3).value
U_Setting.fax = rs.Fields(4).value
U_Setting.zip = rs.Fields(5).value
U_Setting.lxr = rs.Fields(6).value
U_Setting.Servername = rs.Fields(7).value
U_Setting.Databasename = rs.Fields(8).value
U_Setting.odbc = rs.Fields(9).value
U_Setting.username = rs.Fields(11).value
U_Setting.Password = rs.Fields(12).value
rs.Close
conn.Close
GoTo inerr
ine:
M_Error = "数据库错误号:" & Err.Number & " 错误内容:" & Err.Description
MsgBox M_Error, , "错误信息"
inerr:
Set rs = Nothing
End Function
select case Combo1.Text
case "文书编号"
sname = "bh"
case "文书名称"
sname = "mc"
...end select替换的。
select 字段1,字段2... from 表名1,表名2... where 条件1 and 条件2 and ....