问题描述
我正在尝试创建一个可以作为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
推荐答案
这篇关于Querydef的可选参数，默认内置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！