Creating a Recordset Using a Range of Dates

[BitCounter]

How do I create a &quot;SELECT statement that allows me to get records using 2 dates (obtained from textboxes) and get all records whose date appears within that range?<br>
<br>
For Example: The user selects to print out all records with a particular date. My program asks them to input a beginning date and an ending date. I then want the SQL to select all records that occur between these 2 dates. I can't find examples of this anywhere.<br>
<br>
Also would like to know the proper syntax for an SQL statement that obtains all records with a date prior to the one entered by the user (e.g., if the user enters 03/20/00, then my query should get all records with dates prior to 03/20/00)...any help would be greatly appreciated!

 

[JimHorton]

The easiest way is to put the parameters directly into the SQL, via the qbe grid. In the Criteria row for the Date Column in question, put<br>
Between [Enter Start Date] and [Enter End Date]<br>
<br>
If you have a form, you could do this (in whatever event you trigger the query, probably a ''Find&quot; button or &quot;run query&quot; button, etc)<br>
<br>
dim sq as string<br>
sq = &quot;Select * from SomeTable Where SomeDate &gt;= #&quot; & me!txtStartDate & &quot;# AND SomeDate &lt;= #&quot; & me!txtEndDate & &quot;#&quot;<br>
docmd.runsql sq<br>
<br>
To combine both--where you could either just open the query from the db window, or use it via this form, create the query and use the parameters as in example 1, then in the form, do this:<br>
<br>
Dim qd as querydef<br>
set qd = currentdb.querydefs(&quot;ThatQuerysName&quot<br>
qd.Parameters(&quot;[Enter Start Date]&quot = me!txtStartDate<br>
qd.Parameters(&quot;[Enter End Date]&quot = me!txtEndDate<br>
Docmd.openquery qd.name<br>
<br>
--Jim

 

[FailSafe]

Be careful when using dates with SQL and ACCESS databases.<br>
Firstly SQL is ALWAYS in the format MM/DD/YY<br>
While Access uses the date format in the regional settings.<br>
When / if you internationalize your apps these are important considerations.

 

[MikeLacey]

With sql you can say:<br>
<br>
Where date_field Between '01/01/2000' And '01/31/2000'<br>
<br>
which is a bit easier than all that &lt;= &gt;= stuff<br>
<br>
Mike<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>