Querydef的可选参数，默认内置 [英] Optional Parameter for Querydef with Default Built In
问题描述
我正在尝试创建一个可以作为querydef对象加载的查询
，但如果我不想，则不必为参数赋值。
通常在VBA中使用参数查询时，我的代码会像
这样：
dim qry as dao.querydef
set qry = currentdb.querydefs（" myquery"）
qry.parameters（" Par1"）=" blah"
我想要做的是在查询中构建一个值，如果我不手动设置参数的值，则使用
。在这种情况下，
Par1将被设置为blahblah。除非我专门将参数
设置为blah。我知道我可以使用一个表单来解决这个问题，但是我会希望我的查询尽可能自给自足，以便在
其他表单/报表/模块中重用在这些地方调用查询。
这是我已为此设置的标准：
如iif（isnull（[Par1]），" *，[Par1]）
在这种情况下，如果我没有在参数中输入任何内容，或者我输入了
星号我得到的所有记录，如果我输入
的全文参数，我只得到匹配该参数的记录。但是，
这仍然需要我在代码中设置参数，或者当我从查询gui运行查询时手动设置
。
一种解决方法，在这种情况下很简单，但是反对谷物
无论如何，看起来像这样：
公共函数setQueryDefaults（qryName作为字符串）作为DAO.QueryDef
Dim qry作为DAO.QueryDef
设置qry = CurrentDb.QueryDefs（qryName）
Dim i As Integer
For i = 0 to qry.Parameters.Count - 1
qry.Parameters（i）= Null
Next i
设置setQueryDefaults = qry
结束函数
当然可以扩展来处理querydef对象初始化
参数（例如，dbOpenDynaset），但这只会增加程序的
开销。此外，它假设
查询中的参数已正确设置为接受空值。
当然从此设置迁移到默认设置
参数以所需的方式支持将有点像一个b $ b b梦魇，假设我重写了下一个数据库引擎的查询。
Marc
I am trying to create a query that can be loaded as a querydef object
but not having to assign values to the parameters if I don''t want to.
Normally when using a parameter query in VBA my code would go something
like this:
dim qry as dao.querydef
set qry = currentdb.querydefs("myquery")
qry.parameters("Par1") = "blah"
What I would like to do is build into the query a value that would be
used if I don''t manually set the value of the parameter. In this case,
Par1 would be set to "blahblah" unless I specifically set the parameter
to "blah". I know I can somewhat kludge this using a form but I would
like to keep my queries as self-sufficient as possible for reuse in
other forms/reports/modules when the query is called in those places.
This is the criteria I already set for this:
like iif(isnull([Par1]),"*",[Par1])
In this case, if I do not enter anything into the parameter, or I put in
the asterisk I get all the records, while if I put in the full text of
the parameter I get only the record matching that parameter. However,
this still requires me to set the parameter, either in code or manually
when I run the query from the query gui.
A workaround, which in this case is simple, but goes against the grain
anyway, looks like this:
Public Function setQueryDefaults(qryName As String) As DAO.QueryDef
Dim qry As DAO.QueryDef
Set qry = CurrentDb.QueryDefs(qryName)
Dim i As Integer
For i = 0 To qry.Parameters.Count - 1
qry.Parameters(i) = Null
Next i
Set setQueryDefaults = qry
End Function
Of course it can be expanded to handle querydef object initialization
parameters (e.g., dbOpenDynaset), but this would just add to the
overhead to the program. Also, it assumes that the parameters on the
queries are properly set to accept null values.
Of course migrating from such this setup to one in which default
parameters are supported in the desired fashion would be somewhat of a
nightmare, assuming I rewrite the queries for the next database engine.
Marc
推荐答案
你可以做这样的事情---
dim qry as dao.querydef
dim param作为参数
dim SQLStrBlah As String
dim SQLStrBlahBlah As String
set SQLStrBlah =" Select .. ......其中......"
设置SQLStrBlahBlah ="选择......哪里......"
set qry = currentdb.querydefs（" ; myquery"）
如果< Whatever>然后
qry.sql = SQLStrBlah
否则
qry.sql = SQLStrBlah
结束如果
对于qry.parameters中的每个参数
param.value = EVAL（param.name）
净参数
-
PC数据表
您的资源以获取Access，Excel和Word应用程序的帮助
re ****** @ pcdatasheet.com
www.pcdatasheet .com
如果您无法在新闻组中获得所需的帮助，我可以帮助您支付非常合理的费用
。超过1000名访问用户来找我寻求帮助。
需要一个月历或7天日历吗？需要预约安排？需要
房间预订安排？需要员工的工作安排？联系我！
" Marc DVer" <毫安** @ not.in.use>在消息中写道
新闻：Xs ******************* @ fe10.lga ...
You can do something like this ---
dim qry as dao.querydef
dim param as parameter
dim SQLStrBlah As String
dim SQLStrBlahBlah As String
set SQLStrBlah = "Select ..... Where..."
set SQLStrBlahBlah = "Select ..... Where..."
set qry = currentdb.querydefs("myquery")
If <Whatever > Then
qry.sql = SQLStrBlah
Else
qry.sql = SQLStrBlah
End If
For each param in qry.parameters
param.value = EVAL(param.name)
Net param
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
If you can''t get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!
"Marc DVer" <ma**@not.in.use> wrote in message
news:Xs*******************@fe10.lga...
我是尝试创建一个可以作为querydef对象加载的查询，但如果我不想，则不必为参数赋值。
通常在使用参数查询时VBA我的代码会像这样：
dim qry as dao.querydef
设置qry = currentdb.querydefs（" myquery"）
qry.parameters （Par1）=blah
我想要做的是在查询中构建一个可以使用的值
如果我不手动设置值参数。在这种情况下，Par1
将被设置为blahblah。除非我专门将参数设置为
blah。我知道我可以使用表单来解决这个问题，但我希望在这些地方调用查询时，尽可能保持我的查询自给自足，以便在其他
表单/报表/模块中重用。
这是我已为此设定的标准：
如iif（isnull（[Par1]），*，[Par1]）
在这种情况下，如果我没有在参数中输入任何内容，或者我输入了星号，我得到了所有记录，而如果我输入
参数的全文，我只得到记录匹配该参数。但是，这仍然需要我在代码中设置参数，或者在我从查询gui运行查询时手动设置参数。
一种解决方法，在这种情况下是简单，但反对谷物
无论如何，看起来像这样：
公共函数setQueryDefaults（qryName As String）作为DAO.QueryDef
Dim qry作为DAO.QueryDef
设置qry = CurrentDb.QueryDefs（qryName）
Dim i As Integer
对于i = 0 to qry.Parameters.Count - 1
qry.Parameters（i）= Null
接下来我设置setQueryDefaults = qry
结束函数当然它可以扩展来处理querydef对象的初始化参数（例如dbOpenDynaset），但这只是添加到程序的开销。此外，它假定查询上的参数被正确设置为接受空值。
当然从这种设置迁移到支持默认
参数的设置假设我重写了下一个数据库引擎的查询，那么所需的时尚会有点噩梦。
Marc
I am trying to create a query that can be loaded as a querydef object but
not having to assign values to the parameters if I don''t want to.
Normally when using a parameter query in VBA my code would go something
like this:
dim qry as dao.querydef
set qry = currentdb.querydefs("myquery")
qry.parameters("Par1") = "blah"
What I would like to do is build into the query a value that would be used
if I don''t manually set the value of the parameter. In this case, Par1
would be set to "blahblah" unless I specifically set the parameter to
"blah". I know I can somewhat kludge this using a form but I would like
to keep my queries as self-sufficient as possible for reuse in other
forms/reports/modules when the query is called in those places.
This is the criteria I already set for this:
like iif(isnull([Par1]),"*",[Par1])
In this case, if I do not enter anything into the parameter, or I put in
the asterisk I get all the records, while if I put in the full text of the
parameter I get only the record matching that parameter. However, this
still requires me to set the parameter, either in code or manually when I
run the query from the query gui.
A workaround, which in this case is simple, but goes against the grain
anyway, looks like this:
Public Function setQueryDefaults(qryName As String) As DAO.QueryDef
Dim qry As DAO.QueryDef
Set qry = CurrentDb.QueryDefs(qryName)
Dim i As Integer
For i = 0 To qry.Parameters.Count - 1
qry.Parameters(i) = Null
Next i
Set setQueryDefaults = qry
End Function
Of course it can be expanded to handle querydef object initialization
parameters (e.g., dbOpenDynaset), but this would just add to the overhead
to the program. Also, it assumes that the parameters on the queries are
properly set to accept null values.
Of course migrating from such this setup to one in which default
parameters are supported in the desired fashion would be somewhat of a
nightmare, assuming I rewrite the queries for the next database engine.
Marc
" PC数据表" <无**** @ nospam.spam> schreef在bericht新闻：t0 **************** @ newsread1.news.atl.earthli nk.net ...
<剪断了消息和所有的广告内容>
到OP：小心这个家伙!!
史蒂夫只是做*不关心关于新闻组。他根本没有任何道德规范*。
史蒂夫*只关心赚*钱*，他表现得好像这些团体是他的私人狩猎场。
- 他一遍又一遍地滥用这个团体和其他人寻找工作和广告
- 当他们问他时，他在这里侮辱很多人他要停止这个
- 他发布了史蒂夫，罗恩，汤姆，雷切尔，凯西，克里斯汀，希瑟和???在提问时
（最新的''明星'''''''访问资源''和汤姆没有* **@email.com 和Andy）
- 他试图用免费代码出售一张CD（
"PC Datasheet" <no****@nospam.spam> schreef in bericht news:t0****************@newsread1.news.atl.earthli nk.net...
<snipped the message and all the advertising stuff>
To the OP: Beware of this guy!!
Steve just does *not* care about the newsgroups. He has *no ethics at all*.
Steve *only* cares about making *money*, and he acts as if the groups are his private hunting ground.
-- He abuses this group and others for job-hunting and advertising over and over again
-- He is insulting lots of people here when they ask him to stop this
-- He posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ??? while asking questions
(the latest ''star''s'': ''Access Resource'' and Tom no***@email.com and Andy)
-- He tries to sell a CD (
125， - ）这些团体聚集在这里
- 甚至有一个关于他的诈骗警报，最近在To all中解释过：
http://groups.google。 com / group / comp .... 954261f9？hl = en
- 最近很明显他一直在向无辜的人发出垃圾邮件问题：
http://groups.google.com /group/comp....3e5f58ad?hl=zh-CN
为什么ANYBODY会相信像他这样的人并雇用他？
********************************************** **** ******
解释和这个答案的更多链接：
http://home.tiscali.nl/arracom/stopsteve.html
Arno R
125,--) with FREE code he gathered from these groups here
-- There even has been a ''Scam-alert'' about him which has been explained recently in the thread ''To all'':
http://groups.google.com/group/comp....954261f9?hl=en
-- Also recently it became clear that he has been spamming innocent people asking questions:
http://groups.google.com/group/comp....3e5f58ad?hl=en
So why would ANYBODY ever trust a person like him and hire him?
************************************************** ******
Explanation and more links on this answer:
http://home.tiscali.nl/arracom/stopsteve.html
Arno R
这篇关于Querydef的可选参数，默认内置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
