HOW TO QUERY A TABLE USING A DATE RANGE???

[HerickP]

HI,<br><br>I know there is a way to query a table where u can specify a criteria ( for example a date) so the query results will show records particular to the date specified. But what about a range, for instance, a week ( like from 05/15 to 05/19)????? I dunno how to state this in code, or in a query, could u guys help me??? Thanks again@!

 

[Rubius]

in critera cell<br><br>Between #2/2/93# And #12/1/93#

 

[HerickP]

Cool Man, Thanks!!! Could I go further, like, have some sort of code so the user can imput the date range, instead of having to have pre-defined ones???? Some sort of form or popup box, where they would imput the date range and have the results. Challenging???? HEHE, thanks a lot guys!!

 

[Rubius]

Between [date1] And [date2]<br><br>enter into the boxes as 01/05/00

 

[HerickP]

I didnt understand..........

 

[Rubius]

ok, instead of <br>Between #2/2/93# And #12/1/93# <br>put in<br>Between [date1] And [date2]<br><br>now it will prompt you with a box and a place you can type stuff. in the first one type something like<br>05/01/00<br>then in the second one type<br>05/05/00<br><br>it will list everything from may 1st to may 5th<br><br>have a good one.<br>

 

[3rdRail]

Here is a way to reference form fields back to a query:<br><br>Create a form named&nbsp;&nbsp;Test_frm<br><br>Create two text boxes on the form:<br>&nbsp;&nbsp;Name the first one : StartDate<br>&nbsp;&nbsp;Name the second one: EndDate<br><br>In the criteria field (in the query) type this:<br><br>Between forms!test_frm!StartDate and forms!Test_frm!EndDate<br><br>Now the query will look to the form for the start and end date parameters.&nbsp;&nbsp;&nbsp;Keep in mind you can name the form something other than Test_frm, (I just used that as an example) just make sure you reference it back in the query properly.<br><br>Hope that helps.&nbsp;&nbsp;Good Luck<br><br>3R<br>

 

[DougP]

I took it a step further and put a Combobox on the form in addition to the 2 text boxes.<br>So the combo box had &quot;30 Days&quot;, &quot;60 Days&quot;, &quot;90 Days&quot; in it.<br>So there were just 3 choices in the combo box.<br>When the user clicked on the combobox, it took Todays date and subtracted either 30, 60 or 90 from it and put both of those dates in the textboxes. So you still use the exact same query and or report but the user does not have to manualy calculate the dates.<br>----------------------------- code for that ---------------<br>Private Sub Combo9_AfterUpdate()<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!Text3 = Format(Now, &quot;mm/dd/yy&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!Text5 = DateSerial(Year(Now), Month(Now), Day(Now) - Val(Left(Combo9.Column(0), 3)))<br>End Sub<br>--------------<br>Row source for combox box = &quot;30 Days&quot;;&quot;60 Days&quot;;&quot;90 Days&quot;<br>--------------------<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[DougP]

The ultimate user freindly Control.<br>Put a Calendar control on your form and have them pick both dates that way.<br>Have two buttons on your form in addition to the Calendar control. One button is for the first date and the other button is for the second date. You still have your same 2 textboxes too.<br><br>Code like so<br>----------------<br>Private Sub Command7_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!Text3 = Me!ActiveXCtl2.Value<br>End Sub<br>----------------<br>Private Sub Command8_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!Text5 = Me!ActiveXCtl2.Value<br>End Sub<br>------------------------------<br>Steps to use<br>1st pick a date on the Calendar control<br>2nd Click First Button which puts date selected in first Textbox)<br>3rd Pick a Second date on Calendar Control<br>4th click Second button (puts second date in second textbox)<br>5th Run your query or report.<br><br>To add a calendar control Click in the Hammer/Wrench button on the Toolbar and look for &quot;Calendar Control&quot; in the list (they are alphabetically listed)<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.