动态查询标准(Dynamic Query Criteria)

 我正在尝试使Microsoft Access查询依赖于另一个窗体的文本框中的值。 
 
 这是现在的标准。 基本上，2014年4月1日至2015年3月31日之间的任何日期。这很有效。 
 
>=#2014-04-01# And <#2015-04-01#
 
 我想要一个带有年份的文本框（使用当前示例2014），并使查询条件（2014,2014 + 1）取决于此值。 
 
 我试图拆分上面的语法，然后在标准中连接，如下： 
 
">=#" & "2014" & "-04-01# And <#" & "2015" & "-04-01#"
 
 我收到错误“标准表达式中的数据类型不兼容”。 
 
 1.是否可以在查询条件中连接？ 我也尝试过SQL CONCAT（string1，string2，string3，..），但无济于事。 如果这是可能的，那么我想我可以使用[Forms]！[Form1]。[Textbox1]和（[Forms]！[Form1]。[Textbox1] + 1）来替换岁月。 如果这不可能...... 
 
 2.有更好的方法使查询条件动态化吗？ 
 
 我尝试通过创建具有类似代码的模块来使以下解决方案工作： 
 
Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal sValue as Variant)
m_varQueryParam = sValue
End Function
Public Function GetQueryParam() As Variant
GetQueryParam = m_varQueryParam
End Function

Query:
SELECT * FROM tblYourTable WHERE [FilterField] = GetQueryParam()

The VBA Code to launch the query will look like this.
SetQueryParam "your value here"
DoCmd.OpenQuery "qryYourQueryHere"
 
 但我根本不明白如何让这个工作。 
 
 编辑：我创建了一个简单的访问数据库，试图让它工作。 
 
 
 
 Textbox1，默认值= Date（） 
 
 
 bSave，按钮 
 
 
 tDateInfo，表：日期（日期/时间），信息（文本）以及随机日期和信息。 
 
 
 查询1： 
 
 
 SELECT tDateInfo.date, tDateInfo.info FROM tDateInfo WHERE (((tDateInfo.date)=GetQueryParam())); 
 
 这是表单的vba代码 
 
Option Compare Database

Private Sub bSave_Click()
sValue = Me.TextBox1.Value
SetQueryParam (sValue)
End Sub
 
 这是模块vba代码 
 
Option Compare Database
Option Explicit
'is this necessary?

Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal sValue As Variant)
m_varQueryParam = sValue
End Function
Public Function GetQueryParam() As Variant
GetQueryParam = m_varQueryParam
End Function
 
 查询条件是GetQueryParam() 
 
 感谢您的帮助。 

I'm trying to make a Microsoft Access query depend on a value in another form's textbox.
 
This is the criteria, as it is now. Basically, any date between April 1st 2014, and March 31st 2015. This works well.
 
>=#2014-04-01# And <#2015-04-01#
 
I'd like to have a textbox with the year (with the current example 2014), and make the query criteria (2014, 2014+1) depend on this value.
 
I've tried to split the above syntax, then concatenate in the criteria, as such:
 
">=#" & "2014" & "-04-01# And <#" & "2015" & "-04-01#"
 
And I get an error "Data types in the criterion expression are incompatible".
 
1. Is it possible to concatenate in the query criteria? I have also tried the SQL CONCAT(string1,string2,string3,..), to no avail. If this is possible, then I guess I can use [Forms]![Form1].[Textbox1] and ([Forms]![Form1].[Textbox1] + 1) to replace the years. If this is not possible...
 
2. Is there a better way to make the query criteria dynamic?
 
I tried to make the following solution work by creating a module with similar code:
 
Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal sValue as Variant)
m_varQueryParam = sValue
End Function
Public Function GetQueryParam() As Variant
GetQueryParam = m_varQueryParam
End Function

Query:
SELECT * FROM tblYourTable WHERE [FilterField] = GetQueryParam()

The VBA Code to launch the query will look like this.
SetQueryParam "your value here"
DoCmd.OpenQuery "qryYourQueryHere"
 
But I simply do not understand how to get this to work.
 
EDIT: I created a simple access database, to try to get this to work.
 
 
 
Textbox1, default value =Date()
 
 
bSave, button
 
 
tDateInfo, table: date (date/time), info (text) with random dates and info.
 
 
Query1:
 
 
SELECT tDateInfo.date, tDateInfo.info FROM tDateInfo WHERE (((tDateInfo.date)=GetQueryParam())); 
 
Here's the form's vba code
 
Option Compare Database

Private Sub bSave_Click()
sValue = Me.TextBox1.Value
SetQueryParam (sValue)
End Sub
 
Here's the modules vba code
 
Option Compare Database
Option Explicit
'is this necessary?

Private m_varQueryParam As Variant
Public Function SetQueryParam(ByVal sValue As Variant)
m_varQueryParam = sValue
End Function
Public Function GetQueryParam() As Variant
GetQueryParam = m_varQueryParam
End Function
 
And the query criteria is GetQueryParam()
 
Thank you for your help.

原文：https://stackoverflow.com/questions/23934581