为每个文本框定义一个Boolean变量(默认值为False),如:为Text1定义dim blnText1 as Boolean在Text1的Validate事件中判断Text1.Text是否为空,为空blnText1=False,非空blnText1=TrueSql语句可以这样写strSql="SELECT 字段名 FROM 表名 WHERE 1=1"(总之能访问所有的记录就行)条件查询的语句这样写 If blnText1 Then strSql=strSql & "AND 字段名=" & Text1.Text End If 依此类推。 虽然比较麻烦,但没有多余的控件,用户使用比较简单。
可以, 你看看 Dim str1 As String Dim rs1 As ADODB.Recordset Dim iswhere As Boolean Dim ViewItems As ListItem Dim str2 As String
If CombLine.Text <> "" Then iswhere = True str2 = " where voyage = '" & CombLine.Text & "'" End If If CombShipCompany.Text <> "" Then If iswhere Then str2 = str2 & " and shipcompany = '" & CombShipCompany.Text & "'" Else str2 = " where shipcompany = '" & CombShipCompany.Text & "'" End If End If If CombTerminal.Text <> "" Then If iswhere Then str2 = str2 & " and destport = '" & CombTerminal.Text & "'" Else str2 = " where destport = '" & CombTerminal.Text & "'" End If End If If CombCountry.Text <> "" Then If iswhere Then str2 = str2 & " and country = '" & CombCountry.Text & "'" Else str2 = " where country = '" & CombCountry.Text & "'" End If End If
str1 = str1 & str2 str1 = str1 & " order by voyage,shipcompany,destport,country"
strSQL="SELECT * FROM Table" if txtID.text<>"" then bStart=true strSQL=strSQL & " ID=" & txtID.text endif if txtName.text<>"" then if bStart then strSQL=strSQL & " AND Name=" & txtName.text else strSQL=strSQL & " Name=" & txtName.text bStart=true endif'呵呵写着玩
我是这样做的,你可以参考一下
你把你的字段列出。当用户选择了一个字段后。你就提示用户输入查询条件。
1、当用户从你的字段列表中双击"姓名"字段后。你叫TEXT1得到焦点。让用户输入要查找的人名。这是单字段查询。
2、如果用户想多字段查询。你可以让用户再点你的一个"not\and\or"列表。选择是否再加上条件。然后再重复1
这样最终会形成一个查询语句。把它付给一个变量。
这只是我的笨方法,
:)
If blnText1 Then
strSql=strSql & "AND 字段名=" & Text1.Text
End If
依此类推。
虽然比较麻烦,但没有多余的控件,用户使用比较简单。
你看看
Dim str1 As String
Dim rs1 As ADODB.Recordset
Dim iswhere As Boolean
Dim ViewItems As ListItem
Dim str2 As String
str1= "selectvoyage,shipcompany,destport,country,USD20,USD40,USD40HC,USDother,we ekly,baf_20,baf_40,baf_hc40,baf_other,effdate"
str1 = str1 & ",order20,order40,order40HC,orderOther,days"
str1 = str1 & ",afee_name1,afee1,afee_name2,afee2,afee_name3,afee3,afee_name4,afee4,afee_name5,afee5"
str1 = str1 & ",midport,destcode,fnote"
str1 = str1 & " from myfreight"
If CombLine.Text <> "" Then
iswhere = True
str2 = " where voyage = '" & CombLine.Text & "'"
End If
If CombShipCompany.Text <> "" Then
If iswhere Then
str2 = str2 & " and shipcompany = '" & CombShipCompany.Text & "'"
Else
str2 = " where shipcompany = '" & CombShipCompany.Text & "'"
End If
End If
If CombTerminal.Text <> "" Then
If iswhere Then
str2 = str2 & " and destport = '" & CombTerminal.Text & "'"
Else
str2 = " where destport = '" & CombTerminal.Text & "'"
End If
End If
If CombCountry.Text <> "" Then
If iswhere Then
str2 = str2 & " and country = '" & CombCountry.Text & "'"
Else
str2 = " where country = '" & CombCountry.Text & "'"
End If
End If
str1 = str1 & str2
str1 = str1 & " order by voyage,shipcompany,destport,country"
if txtID.text<>"" then
bStart=true
strSQL=strSQL & " ID=" & txtID.text
endif
if txtName.text<>"" then
if bStart then
strSQL=strSQL & " AND Name=" & txtName.text
else
strSQL=strSQL & " Name=" & txtName.text
bStart=true
endif'呵呵写着玩
sqlstring = "SELECT UserNO,SerialNO,MachineType,UserName,OpenDate,PayDate,MatureDate,ServiceStatus,CurrentPOS,AgentName FROM user "
If Check1.Value Then TempString = "len(UserName)>0 AND "
If Trim(MEBox1(0).text) <> "" Then TempString = "UserNO=" & Trim(MEBox1(0).text) & " AND "
TempString = TempString & "MachineType like '" & Trim(Combo1(0).text) & "*' AND "
TempString = TempString & "agentname like '" & Trim(Combo2.text) & "*' and "
TempString = TempString & "CurrentPOS like'" & Trim(Combo1(1).text) & "*' AND "
TempString = TempString & "UserName Like '" & Trim(MEBox1(1).text) & "*' AND "
If Trim(Combo1(2).text) <> "" Then TempString = TempString & "ServiceStatus='" & Trim(Combo1(2).text) & "' AND "
If Trim(MEBox1(2).text) <> "" Then TempString = TempString & "SerialNO = " & Trim(MEBox1(2).text) & " AND "
TempString = TempString & "OpenDate BETWEEN #" & Format(DTPicker1(0).Value, "yyyy/m/d") & "# AND #" & Format(DTPicker1(1).Value, "yyyy/m/d") _
& "# AND PayDate BETWEEN #" & Format(DTPicker1(2).Value, "yyyy/m/d") & "# AND #" & Format(DTPicker1(3).Value, "yyyy/m/d") _
& "# AND MatureDate BETWEEN #" & Format(DTPicker1(4).Value, "yyyy/m/d") & "# AND #" & Format(DTPicker1(5).Value, "yyyy/m/d") & "# "
If Trim(TempString) <> "" Then sqlstring = sqlstring & "WHERE " & TempString
效果如图:
http://microinfo.top263.net/Images/grid.gif