高级查询 [英] Advanced Queries
问题描述
大家好，
我只是有点卡住......我所拥有的是带刻度框的表格和
文本框和它上面的东西以及搜索按钮一样。
我希望能够做的是让用户打勾并填写
一些这些空间和当他们打到seacrh我希望它能做的是
只显示已经选择的那些colums，如果他们没有选择
它显示的任何东西消息说请选择一个方框
如果有可能，那就太棒了
谢谢
Hi all,
I''m just a little bit stuck... what i have is a form with tick boxs and
text boxs and stuff like that on it and a search button.
What I would like to be able to do is for the user to tick and fill in
some of the spaces and when they hit seacrh i would like it to do is
only show those colums that the has selected and if they don''t select
anything it shows a message saying "Please select a box"
If it is possible, that would be great
Thanks
推荐答案
Wo ******** @ hotmail.com 写道：
大家好，
我只是有点卡住......我所拥有的是带刻度框的表格
和文本框以及类似的东西和搜索按钮。
我希望能够做的是让用户勾选并填写我一些空间，当他们打到seacrh我想它要做的只是显示那些已经选择的colums，如果他们没有选择
任何它显示一条消息说请选择一个方框
如果有可能，那就太好了
谢谢
Hi all,
I''m just a little bit stuck... what i have is a form with tick boxs
and text boxs and stuff like that on it and a search button.
What I would like to be able to do is for the user to tick and fill in
some of the spaces and when they hit seacrh i would like it to do is
only show those colums that the has selected and if they don''t select
anything it shows a message saying "Please select a box"
If it is possible, that would be great
Thanks
为了简化操作，请将屏幕控件命名为与底层的
列相同。然后迭代屏幕上的控件。将代码附加到您的
搜索按钮，如下所示：
''VARS
dim sqlStr as string，ctl as Control
sqlStr =""
''ITERATE CONTROLS
for me.Controls每个ctl
如果ctl.ControlType = acCheckBox那么
如果ctl = True那么
sqlStr = sqlStr& ctl.Name& "，"
endif
elseif ctl.ControlType = acTextBox then
if not isnull（ctl）then
sqlStr = sqlStr& ctl.Name& "，"
endif
endif
next ctl
''NO CONTROLS CHOSEN或已完成
如果sqlStr =""那么
msgbox"请选择一个方框,,系统名称"
退出子
endif
''TRIM AND DROP TRAILING COMMA
sqlStr = trim（sqlStr）
sqlStr = left（sqlStr，len（sqlStr）-1）
''显示数据
sqlStr =" SELECT" &安培; sqlStr& " FROM表WHERE blah blah;
Me.Subform.RecordSource = sqlStr
注意：此代码不会在SQL语句中构建任何WHERE标准 - 它
只检查控件是真还是非空。
To make it easier, name the screen controls the same as the underlying
columns. Then iterate the controls on the screen. Attach code to your
Search button, something like this:
''VARS
dim sqlStr as string, ctl as Control
sqlStr = ""
''ITERATE CONTROLS
for each ctl in Me.Controls
if ctl.ControlType = acCheckBox then
if ctl = True then
sqlStr = sqlStr & ctl.Name & ", "
endif
elseif ctl.ControlType = acTextBox then
if not isnull(ctl) then
sqlStr = sqlStr & ctl.Name & ", "
endif
endif
next ctl
''NO CONTROLS CHOSEN OR COMPLETED
if sqlStr = "" then
msgbox "Please select a box",,"System Name"
exit sub
endif
''TRIM AND DROP TRAILING COMMA
sqlStr = trim(sqlStr)
sqlStr = left(sqlStr,len(sqlStr)-1)
''SHOW DATA
sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
Me.Subform.RecordSource = sqlStr
Note: this code doesn''t build any WHERE criteria into the SQL statement - it
just checks if the control is True or Not Null.
这可能会让人很困惑。我建议将Control命名为
名称底层字段加上前缀ctl对于控制。
我使用更细粒度的Reddick命名约定，但是如果你使用
" ctl"它可以防止混淆同时使用底层字段和显示具有相同名称的控件，并且更容易在代码中使用。
Larry Linson
Microsoft Access MVP
" DFS" < nospam@dfs_.com>在消息中写道
新闻：LR ************* @ bignews1.bellsouth.net ...
That can be very confusing. I''d suggest naming the Control to the name of
the underlying Field plus a prefix "ctl" for Control.
I use a more granular form of the Reddick Naming Convention, but if you use
"ctl" it will prevent the confusion of having both the underlying field and
displaying control having the same name, and be easier to use in code.
Larry Linson
Microsoft Access MVP
"DFS" <nospam@dfs_.com> wrote in message
news:LR*************@bignews1.bellsouth.net...
Wo ******** @ hotmail.com 写道：
大家好，
我只是有点卡住......我所拥有的是一个带有勾选框的表单和文本框以及类似的东西和搜索按钮。
我希望能够做的是让用户勾选并填写
一些空格，当他们点击seacrh我想它要做的是
只显示那些colums选择，如果他们没有选择
任何东西，它会显示一条消息请选择一个方框
如果有可能，那就太棒了
谢谢
Hi all,
I''m just a little bit stuck... what i have is a form with tick boxs
and text boxs and stuff like that on it and a search button.
What I would like to be able to do is for the user to tick and fill in
some of the spaces and when they hit seacrh i would like it to do is
only show those colums that the has selected and if they don''t select
anything it shows a message saying "Please select a box"
If it is possible, that would be great
Thanks
为了简化操作，请将屏幕控件命名为与底层
列相同。然后迭代屏幕上的控件。将代码附加到您的
搜索按钮，如下所示：
''VARS
dim sqlStr as string，ctl as Control
sqlStr =""
如果ctl = True那么
sqlStr = sqlStr& ctl.Name& "，"
endif
elseif ctl.ControlType = acTextBox然后
如果不是isnull（ctl）那么
sqlStr = sqlStr& ctl.Name& "，"
endif
endif
下一个ctl
''没有控制选择或完成
如果sqlStr =""然后
msgbox请选择一个方框，系统名称
退出子页
''TRIM AND DROP TRAILING COMMA
sqlStr = trim（sqlStr）
sqlStr = left（sqlStr，len（sqlStr）-1）
''显示数据
sqlStr =" SELECT" &安培; sqlStr& " FROM表WHERE blah blah;
Me.Subform.RecordSource = sqlStr
注意：此代码不会在SQL语句中构建任何WHERE标准 -
它只检查控件是真还是非空。
To make it easier, name the screen controls the same as the underlying
columns. Then iterate the controls on the screen. Attach code to your
Search button, something like this:
''VARS
dim sqlStr as string, ctl as Control
sqlStr = ""
''ITERATE CONTROLS
for each ctl in Me.Controls
if ctl.ControlType = acCheckBox then
if ctl = True then
sqlStr = sqlStr & ctl.Name & ", "
endif
elseif ctl.ControlType = acTextBox then
if not isnull(ctl) then
sqlStr = sqlStr & ctl.Name & ", "
endif
endif
next ctl
''NO CONTROLS CHOSEN OR COMPLETED
if sqlStr = "" then
msgbox "Please select a box",,"System Name"
exit sub
endif
''TRIM AND DROP TRAILING COMMA
sqlStr = trim(sqlStr)
sqlStr = left(sqlStr,len(sqlStr)-1)
''SHOW DATA
sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
Me.Subform.RecordSource = sqlStr
Note: this code doesn''t build any WHERE criteria into the SQL statement -
it
just checks if the control is True or Not Null.
Larry Linson写道：
Larry Linson wrote:
这可以是很混乱。
为什么？
我建议将控件命名为基础字段的
名称加上前缀 CTL"控制。
访问默认表格控件名称为列名，所以除非他创建
表格没有记录源，否则它已经设置了名称。
我使用更细粒度的Reddick命名约定，但是如果你使用ctl它将防止混淆同时具有相同名称的基础字段和显示控件，并且更容易在代码中使用。
你的意思是难以使用。你必须剥离ctl。每次你要
想用控制名替换成SQL。
Larry Linson
Microsoft Access MVP
DFS < nospam@dfs_.com>在消息中写道
新闻：LR ************* @ bignews1.bellsouth.net ...
That can be very confusing.
Why?
I''d suggest naming the Control to the
name of the underlying Field plus a prefix "ctl" for Control.
Access defaults form control names to the column names, so unless he created
the form with no recordsource it already had the names set.
I use a more granular form of the Reddick Naming Convention, but if
you use "ctl" it will prevent the confusion of having both the
underlying field and displaying control having the same name, and be
easier to use in code.
You mean harder to use. You would have to strip off "ctl" each time you
wanted to substitute the control name into the SQL.
Larry Linson
Microsoft Access MVP
"DFS" <nospam@dfs_.com> wrote in message
news:LR*************@bignews1.bellsouth.net...
Wo ******** @ hotmail.com 写道：
大家好，
我只是有点卡住......我所拥有的是一个带有勾选框的表格和文本框以及类似的东西和搜索按钮。
我想要的是什么喜欢能够做的是让用户在某些空间中打勾并填充
当他们点击seacrh时我希望它只显示已经选择的那些colums，如果他们
不要选择任何显示消息的消息请选择一个
框
如果有可能，那就太好了
谢谢
为简化起见，请将屏幕控件命名为
基础列。然后迭代屏幕上的控件。
将代码附加到您的搜索按钮，如下所示：
''VARS
将sqlStr作为字符串，ctl作为控件
sqlStr =""
为I.Controls中的每个ctl设置ITERATE CONTROLS
如果ctl.ControlType = acCheckBox则
如果ctl = True则
sqlStr = sqlStr& ctl.Name& "，"
endif
elseif ctl.ControlType = acTextBox然后
如果不是isnull（ctl）那么
sqlStr = sqlStr& ctl.Name& "，"
endif
endif
下一个ctl
''没有控制选择或完成
如果sqlStr =""然后
msgbox请选择一个方框，系统名称
退出子页
''TRIM AND DROP TRAILING COMMA
sqlStr = trim（sqlStr）
sqlStr = left（sqlStr，len（sqlStr）-1）
''显示数据
sqlStr =" SELECT" &安培; sqlStr& " FROM表WHERE等等等等;
Me.Subform.RecordSource = sqlStr
注意：此代码没有在SQL
语句中构建任何WHERE标准 - 它
只检查控件是True还是No Null。
To make it easier, name the screen controls the same as the
underlying columns. Then iterate the controls on the screen.
Attach code to your Search button, something like this:
''VARS
dim sqlStr as string, ctl as Control
sqlStr = ""
''ITERATE CONTROLS
for each ctl in Me.Controls
if ctl.ControlType = acCheckBox then
if ctl = True then
sqlStr = sqlStr & ctl.Name & ", "
endif
elseif ctl.ControlType = acTextBox then
if not isnull(ctl) then
sqlStr = sqlStr & ctl.Name & ", "
endif
endif
next ctl
''NO CONTROLS CHOSEN OR COMPLETED
if sqlStr = "" then
msgbox "Please select a box",,"System Name"
exit sub
endif
''TRIM AND DROP TRAILING COMMA
sqlStr = trim(sqlStr)
sqlStr = left(sqlStr,len(sqlStr)-1)
''SHOW DATA
sqlStr = "SELECT " & sqlStr & " FROM Table WHERE blah blah;
Me.Subform.RecordSource = sqlStr
Note: this code doesn''t build any WHERE criteria into the SQL
statement - it
just checks if the control is True or Not Null.
这篇关于高级查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
