Change a Query's Criteria with code

[needsolution]

Hello,

Suppose I have a query that pulls the following fields:
1. Date
2. Amount of payments

I also have a form that has:
1. StartDate Text box
2. EndDate Text box
3. OpenQuery Button

What I want to achieve is to enable user to specify the date range for the query.

How can I change the "Criteria" of the Date field of the query from the form's code?

PS: I currently use the following procedure:
1. Open the Query in Design View
2. Use "SendKeys" to move the cursor to the Criteria and type in the value

I just want to know if there's a better alternative. Thanks in advance.

David Hendrawirawan

 

[TTThio]

In the design view "Criteria" just type:

>=[Forms]![FormName]![StartDate] And <=[Forms]![FormName]![EndDate]

It means, it will query data that starts in StartDate and ends in the EndDate.

Good luck.

 

[needsolution]

TTThio,

Thanks for the advise. However, that part I already know. What I am trying to do is to put the above expression as the Query Criteria automatically using VBA.

Currently, I use doCmd.openQuery to open the query in Design mode. Then, I use Sendkeys to move the cursor to the Criteria Column, and then I type in the Expression you gave me using SendKeys. What I am looking for is the syntax to programmatically manipulate the Criteria value using VB. For example, it may look somewhat like this:

Queries![qryAmountByDate].Field![Date].Criteria = &quot;... expression&quot;

(That example is obviously my imaginary example)

Thanks for the response.

 

[cmmrfrds]

If you want to display the query data in a form here is one way to build your sql in the onopen event. An example from one of my programs. Note ReturnCaseTypeJur() is a function in the standard module. It returns a global variable to the query.

Private Sub Form_Open(Cancel As Integer)
'--

Dim SQLString As String, sql1 As String, sql2 As String

''---- Allow filters
Me.AllowFilters = True
Me.ServerFilterByForm = True

sql1 = sql1 + &quot;SELECT ratesID, rateTypeCode, profTimeRate, TWTimeRate, &quot;
sql1 = sql1 + &quot;placeTimeRate, phoneMeterRate, mileRate, dollarLimit, &quot;
sql1 = sql1 + &quot;timeLimit, companyRateID, caseTypeJurID, jurisdictionID, &quot;
sql1 = sql1 + &quot;beginDate , endDate, userStamp, createDate, modifyDate &quot;
sql1 = sql1 + &quot;FROM Rates &quot;
sql1 = sql1 + &quot;WHERE caseTypeJurID = &quot;
sql2 = &quot; ORDER BY beginDate&quot;

SQLString = sql1 & ReturnCaseTypeJur() & sql2
Me.RecordSource = SQLString
Debug.Print &quot; RecordSource = &quot;; Me.RecordSource

End Sub