How to run a query between two dates (a range)

[Indrit]

Hi,
I have a search form created that I want to run a query based on information i select on the table. I have already have added my first condition, however I don't know how to add something like this:

I want to pull information that would be equal or greater than Start Date, and smaller or equal than End Date. Please see my attachments. Once the form selects the hospital from the drop-down list (combo box) after I have placed the code into the criteria, the query will bring all information about that hospital. Now I want to add more criteria such as a date range (example, from 01/25/2014 to 10/01/2014). I would like to use both fields.

[ItsMe]

Should be able to add criteria to the respective field
<=[Forms]![frmSearchSummary]![txtControlName]
and
>=[Forms]![frmSearchSummary]![txtOtherControlName]

[ranman256]

use: between forms!date1 and forms!date2

[ItsMe]

use: between forms!date1 and forms!date2

I think the two controls represent their own respective column within the table.

[Indrit]

it is not working

I am in need of the exact code for each one
I redid the form and query and now I have:

<=[Forms]![frmSubReport2]![dtStartDate]
>=[Forms]![frmSubReport2]![dtEndDate]

It is NOT working. Every time I select the dates respectively on each box on my field it wont run the query although I know I have records on those dates.

[ItsMe]

What is this
frmSubReport2

Is that a subform within a main form?
Maybe
Forms!MainFormName!frmSubReport2.Form!dtStartDate

Or is it a Report Object in a subform container?
Forms!MainFormName!frmSubReport2.Report!dtStartDat e

[Geo21]

Have you formatted the fields in your form as short dates?

[Indrit]

so...
I have a table called "tblFinding"
On this table I have the following fields:

• Hospital
• Director
• Standard
• Code
• Key Indicator
• Type of survey
• Finding
• Start Date
• End Date

I have a query called "qryFinding" that qyeries almost all fields from the above table such as

• Hospital
• Standard
• Code
• Type of survey
• Finding
• Start Date
• End Date

I have a form that feeds off of the above query called: "frmSubReport2" (I decided to call it that)

Now....
I have built the form to run the query based on what hospital I select and based on what Start Date and End Date I select. I have found the way to query the hospital via form by typing the following on the criteria field on the query design view: [Forms]![frmSubReport2]![cboHospitalName] however I cannot find the criteria to type on the Start Date and End date. I need to query all dates that are equal or greater than Start Date and equal or smaller than End Date.


Any help?


Best regards,
Indrit

[ItsMe]

...I need to query all dates that are equal or greater than Start Date and equal or smaller than End Date....

Based on that business rule alone, the code in post #2 is appropriate. If the code in post #2 is not producing the expected result, you need to ask why and adjust your business rule. Then communicate the revised business rule to others if you still need help.

Maybe a revised business rule would look something like this.
I need to query all dates that are equal or greater than Start Date AND < Jan 1, 2015 and (equal or smaller than End Date AND > Dec 31, 2010)

Considering the revised business rule, your code would look something like this.
<=[Forms]![frmSearchSummary]![txtControlName] AND > #12-31-2010#
and another expression for your other column
> =[Forms]![frmSearchSummary]![txtOtherControlName] AND < #01-01-2015#

You can refactor the above code by introducing the BETWEEN operator as suggested in post #3.
BETWEEN #01-01-2011# AND [Forms]![frmSearchSummary]![txtControlName]
and another expression for your other column
BETWEEN [Forms]![frmSearchSummary]![txtOtherControlName] AND #12-31-2014#

[Indrit]

I used your advise

<=[Forms]![frmSearchSummary]![dtStartDate]
>=[Forms]![frmSearchSummary]![dtEndDate]

and it is not working ...

[ItsMe]

... it is not working ...

This does not mean anything. The syntax you posted in post #10 should work. Perhaps it is not giving you the results you expect but, it has to be doing something. Maybe you can post the SQL for your query object. You can view the SQL by using the SQL view option of your Query Object. So, instead of Design View, or Data Sheet view, use SQL view.

Also include a description of what it is doing. Also include a description of what you expected it to do that it is not doing.

[Geo21]

Taking in consideration you want to find the results BETWEEN the dates you input, you are using the &lt; and &gt; wrong. Try switching them around. You want the values to be &gt;= start date and &lt;= end date. You are doing the exact opposite!

[Indrit]

Taking in consideration you want to find the results BETWEEN the dates you input, you are using the &lt; and &gt; wrong. Try switching them around. You want the values to be &gt;= start date and &lt;= end date. You are doing the exact opposite!

I dont understand...

what is &lt; and &gt;

can you please modify my code with yours:

<=[Forms]![frmSearchSummary]![dtStartDate]
>=[Forms]![frmSearchSummary]![dtEndDate]

[ItsMe]

Geo21 is referring to the less than and greater than symbols. However, you have two columns of data. You probably need a Between function for each column. This would require four variables. Post #9 touches on this point. In post 9 I replace two of the four variables with a static date that is hardcoded in the SQL.

[Geo21]

I dont understand...

what is &lt; and &gt;

can you please modify my code with yours:

>=[Forms]![frmSearchSummary]![dtStartDate]
<=[Forms]![frmSearchSummary]![dtEndDate]