VB_ACCESS筛选

[NHGIGGS]

环境:

一CommandButton(cmd_count),一MSHFlexGrid，两OptionButton(Option1,Option2),两ComboBox(Combo1,Combo2)。


希望实现如下功能：

1、If opton1.value=true then 按下‘统计’按钮（cmd_count），MSHFlexGrid列出ACCESS中的表table1中所有性别为“男”的所有数据。

2、If opton1.value=true then 在combo1下拉“所选科目”，combo2下拉“英语”，按下‘统计’按钮（cmd_count），

MSHFlexGrid列出ACCESS中的表table1中所有“选科为英语的全部男性”。


请问以上Sql语句怎么写？？？（用ADODB连接数据库）

[jhone99]

1.
select * from table1 where [性别]='男'

2.
strsql=""
strsql=strsql & "select * " & vbcrlf
strsql=strsql & " from table1 " & vbcrlf
strsql=strsql & " where [性别]='男' " & vbcrlf
strsql=strsql & " and " & trim(combo1.text) & " = '" & trim(combo2.text) & "' "

[clear_zero]

select * from table1 where Gender='Male'


select * from table1 where Gender='Male' and Subject='English'

[booksoon]

Dim cnaccess As New ADODB.Connection, rsaccess As New ADODB.Recordset

cnaccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "\YOURMDB.mdb;" & "Persist Security Info=False"
cnaccess.Open
rsaccess.CursorLocation = adUseClient
rsaccess.Open "delete * from cjk ", cnaccess, 3, 1

sqlstr=" "
If opton1.value=true then
sqlstr=sqlstr & "select * from table1 where 性别='男' "
rsaccess.Open sqlstr, cnaccess, 3, 1
set MSHFlexGrid1.datasoure=rsaccess
end if

~~~~


没法写了~~,opton1和opton2只能有一个选中啊~~~,opton2=true 还要统计出性别为男的??,逻辑上不通啊
按上面的代码自已根据你的意思改吧~~~

[jhone99]

1.

     strsql = ""
     strsql = strsql & " select * " & vbcrlf
     strsql = strsql & "   from table1 " & vbcrlf
     strsql = strsql & "  where 1 =1 " & vbcrlf

     If opton1.value=true then
         strsql = strsql & "    and [性别]='男' "  
     elseIf opton2.value=true then
         strsql = strsql & "    and [性别]='女' "
     end if

[jhone99]

2.

     strsql = "" 
     strsql = strsql & "select *          " & vbcrlf  
     strsql = strsql & "  from table1      " & vbcrlf 
     strsql = strsql & "  where 1 =1 " & vbcrlf

     If opton1.value=true then
         strsql = strsql & "    and [性别]='男' "  
     elseIf opton2.value=true then
         strsql = strsql & "    and [性别]='女' "
     end if 

     if trim(combo1.text)<>"" and trim(combo2.text)<>"" then 
         strsql=strsql & "  and " & trim(combo1.text) & " = '" & trim(combo2.text) & "'  " 
     end if

[贝隆]

帮顶

[getemail]

帮顶
[Quote=引用 6 楼 veron_04 的回复:]
帮顶
[/Quote]

[NHGIGGS]

[Quote=引用 2 楼 clear_zero 的回复:]
select * from table1 where Gender='Male'


select * from table1 where Gender='Male' and Subject='English'
[/Quote]

简单明了~~~~