Filtering between dates

[angiew]

I have a database that contains all of my company's sales information for the last two years. I am making a form that will be used by managers to filter the information and produce a corresponding report. The managers will filter the information by choosing criteria in combo boxes. The form is linked to a query that combines the tables containing the sales information. I need the managers to also be able to specify which beginning and ending dates for sales in the filter criteria. For example, one may want all sales by a certain person from May of 1999 to August of 1999. I would like them to type this date criteria into a box. I have tried to accomplish this by making unbound text boxes on the form and writing an SQL statement for the Sales Date field of the query, but this does not work as the query is run when the form is opened, resulting in a blank form due to the date boxes on the form having no entries. Does anyone have any ideas? Thanks!<br><br>Angie

 

[cmmrfrds]

One way to do it is to have a form where the users enter the criteria. Then pass the criteria to the form that does the query. The form needs to allow filtering but the initial&nbsp;&nbsp;query for the form does not need any criteria filled in. <br><br>Use this syntax in the &quot;criteria selection form&quot; behind a button click event.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim WhereClause as String, DocName as String, QUOTE as String<br>&nbsp;&nbsp;&nbsp;&nbsp;QUOTE = Chr$(34)<br>&nbsp;&nbsp;&nbsp;&nbsp;DocName = &quot;Your Form&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;WhereClause = &quot; aNameinYouTable = &quot; & QUOTE & aVariable & QUOTE<br>&nbsp;&nbsp;&nbsp;'''-- Debug.Print &quot;WhereClause = &quot;; WhereClause<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenForm DocName, , , WhereClause<br><br>This will supply the filter to your form, but dates can be tricky. They need to be surrounded by #. You can put as many join conditions as you want in the WhereClasue. This example puts double quotes around the variable name which is text. Of course, a number would not need the quotes but dates probably need #.<br><br>When you run your form, you can check the filter criteria by looking in the Data properties of the form under Filter.<br>