Dynamic Query Criteria

Question

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.

ron tornambe ron tornambe · Accepted Answer · 2014-05-29T14:35:18

Handling parameters and form fields is a little tricky with VBA. I created a simple table similar to yours as follows: CREATE TABLE DateCalc ( ID AutoNumber, FilterField DateTime )

The following code will return your desired results:

Sub testthis()
    Dim db As dao.database
    Set db = CurrentDb ' use current database
    Dim qd As dao.QueryDef
    Set qd = db.CreateQueryDef("") ' create anaonymous querydef
    ' the SQL statement correctly concatenates the parameter (Useyear) and mm/dd strings
    qd.sql = "SELECT * FROM DateCalc WHERE [FilterField] >= [UseYear]" & "-04-01 And [FilterField] < [UseYear]+1" & "-04-01"
    qd!UseYear = Forms!DateCalc!txtYear ' set the value of se year from the Form WHICH MUST BE OPEN
                                        ' AND the TetBox filled with the year you desire - 2014 for this example.
    Dim rs As dao.recordSet
    Set rs = qd.OpenRecordset
    MsgBox "ID=" & rs(0) & ", Date=" & rs(1)
End Sub

NEW VERSION Sorry, there were a couple of date formatting problems with the first solution that the following code resolves. There are a number of other reasons for the error, so be sure the FormName is "DateCalc" and the TextBox is named "txtYear".

You should be able to generalize the following code for all your queries (do those actually work?). I pass the TableName in now as an example:

Sub DateFilter(TableName As String)
    Dim db As dao.database
    Set db = CurrentDb ' use current database
    Dim qd As dao.QueryDef
    Set qd = db.CreateQueryDef("") ' create anaonymous querydef
    ' the SQL statement correctly concatenates the parameter (Useyear) and mm/dd strings
    Dim UseYear As Integer
    UseYear = Forms!DateCalc!txtYear
    Dim BegDate As Date
    BegDate = CDate(UseYear & "-04-01")
    Dim EndDate As Date
    EndDate = CDate((UseYear + 1) & "-04-01")
    qd.sql = "SELECT * FROM " & TableName & " WHERE [FilterField] >= [UseBegDate] And [FilterField] < [UseEndDate]"
    qd!UseBegDate = BegDate
    qd!UseEndDate = EndDate

    Dim rs As dao.recordSet
    Set rs = qd.OpenRecordset
    Do While Not rs.EOF
      MsgBox "ID=" & rs(0) & ", Date=" & rs(1)
      rs.MoveNext
    Loop
End Sub

Dynamic Query Criteria

2 Answers