Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select... WHERE DateAdd... 1

Status
Not open for further replies.
A1Pat

A1Pat

IS-IT--Management
Jun 7, 2004
454
US
Hello all,

I'm having a requirement to retrieve data from a db where DateAdded is within a start_date and end_date chosen by viewer.

Here is a form I used to get start_date and end_date:

<FORM ACTION="SA_cust_edit.asp" NAME="01" METHOD=GET>
<input type=hidden name=formID value="01">
<tr>
<td align=left nowrap><b>From:</b></td>
<td align=left>
<SELECT NAME="start_month">
<OPTION VALUE=1>January</OPTION>
<OPTION VALUE=2>February</OPTION>
<OPTION VALUE=3>March</OPTION>
<OPTION VALUE=4>April</OPTION>
<OPTION VALUE=5>May</OPTION>
<OPTION VALUE=6>June</OPTION>
<OPTION VALUE=7>July</OPTION>
<OPTION VALUE=8>August</OPTION>
<OPTION VALUE=9>September</OPTION>
<OPTION VALUE=10>October</OPTION>
<OPTION VALUE=11>November</OPTION>
<OPTION VALUE=12>December</OPTION>
</SELECT>
<SELECT NAME="start_day">
<OPTION VALUE=1>1</OPTION>
<OPTION VALUE=2>2</OPTION>
<OPTION VALUE=3>3</OPTION>
<OPTION VALUE=4>4</OPTION>
<OPTION VALUE=5>5</OPTION>
<OPTION VALUE=6>6</OPTION>
<OPTION VALUE=7>7</OPTION>
<OPTION VALUE=8>8</OPTION>
<OPTION VALUE=9>9</OPTION>
<OPTION VALUE=10>10</OPTION>
<OPTION VALUE=11>11</OPTION>
<OPTION VALUE=12>12</OPTION>
<OPTION VALUE=13>13</OPTION>
<OPTION VALUE=14>14</OPTION>
<OPTION VALUE=15>15</OPTION>
<OPTION VALUE=16>16</OPTION>
<OPTION VALUE=17>17</OPTION>
<OPTION VALUE=18>18</OPTION>
<OPTION VALUE=19>19</OPTION>
<OPTION VALUE=20>20</OPTION>
<OPTION VALUE=21>21</OPTION>
<OPTION VALUE=22>22</OPTION>
<OPTION VALUE=23>23</OPTION>
<OPTION VALUE=24>24</OPTION>
<OPTION VALUE=25>25</OPTION>
<OPTION VALUE=26>26</OPTION>
<OPTION VALUE=27>27</OPTION>
<OPTION VALUE=28>28</OPTION>
<OPTION VALUE=29>29</OPTION>
<OPTION VALUE=30>30</OPTION>
<OPTION VALUE=31>31</OPTION>
</SELECT>
<SELECT NAME="start_year">
<OPTION VALUE=1990>1990</OPTION>
<OPTION VALUE=1991>1991</OPTION>
<OPTION VALUE=1992>1992</OPTION>
<OPTION VALUE=1993>1993</OPTION>
<OPTION VALUE=1994>1994</OPTION>
<OPTION VALUE=1995>1995</OPTION>
<OPTION VALUE=1996>1996</OPTION>
<OPTION VALUE=1997>1997</OPTION>
<OPTION VALUE=1998>1998</OPTION>
<OPTION VALUE=1999>1999</OPTION>
<OPTION VALUE=2000>2000</OPTION>
<OPTION VALUE=2001>2001</OPTION>
<OPTION VALUE=2002>2002</OPTION>
<OPTION VALUE=2003>2003</OPTION>
<OPTION VALUE=2004>2004</OPTION>
<OPTION VALUE=2005>2005</OPTION>
<OPTION VALUE=2006 SELECTED>2006</OPTION>
<OPTION VALUE=2007>2007</OPTION>
<OPTION VALUE=2008>2008</OPTION>
<OPTION VALUE=2009>2009</OPTION>
<OPTION VALUE=2010>2010</OPTION>
</SELECT>
</td>
<td align=left nowrap><b>To:</b></td>
<td align=left>
<SELECT NAME="end_month">
<OPTION VALUE=1>January</OPTION>
<OPTION VALUE=2>February</OPTION>
<OPTION VALUE=3>March</OPTION>
<OPTION VALUE=4>April</OPTION>
<OPTION VALUE=5>May</OPTION>
<OPTION VALUE=6>June</OPTION>
<OPTION VALUE=7>July</OPTION>
<OPTION VALUE=8>August</OPTION>
<OPTION VALUE=9>September</OPTION>
<OPTION VALUE=10>October</OPTION>
<OPTION VALUE=11>November</OPTION>
<OPTION VALUE=12>December</OPTION>
</SELECT>
<SELECT NAME="end_day">
<OPTION VALUE=1>1</OPTION>
<OPTION VALUE=2>2</OPTION>
<OPTION VALUE=3>3</OPTION>
<OPTION VALUE=4>4</OPTION>
<OPTION VALUE=5>5</OPTION>
<OPTION VALUE=6>6</OPTION>
<OPTION VALUE=7>7</OPTION>
<OPTION VALUE=8>8</OPTION>
<OPTION VALUE=9>9</OPTION>
<OPTION VALUE=10>10</OPTION>
<OPTION VALUE=11>11</OPTION>
<OPTION VALUE=12>12</OPTION>
<OPTION VALUE=13>13</OPTION>
<OPTION VALUE=14>14</OPTION>
<OPTION VALUE=15>15</OPTION>
<OPTION VALUE=16>16</OPTION>
<OPTION VALUE=17>17</OPTION>
<OPTION VALUE=18>18</OPTION>
<OPTION VALUE=19>19</OPTION>
<OPTION VALUE=20>20</OPTION>
<OPTION VALUE=21>21</OPTION>
<OPTION VALUE=22>22</OPTION>
<OPTION VALUE=23>23</OPTION>
<OPTION VALUE=24>24</OPTION>
<OPTION VALUE=25>25</OPTION>
<OPTION VALUE=26>26</OPTION>
<OPTION VALUE=27>27</OPTION>
<OPTION VALUE=28>28</OPTION>
<OPTION VALUE=29>29</OPTION>
<OPTION VALUE=30>30</OPTION>
<OPTION VALUE=31>31</OPTION>
</SELECT>
<SELECT NAME="end_year">
<OPTION VALUE=1990>1990</OPTION>
<OPTION VALUE=1991>1991</OPTION>
<OPTION VALUE=1992>1992</OPTION>
<OPTION VALUE=1993>1993</OPTION>
<OPTION VALUE=1994>1994</OPTION>
<OPTION VALUE=1995>1995</OPTION>
<OPTION VALUE=1996>1996</OPTION>
<OPTION VALUE=1997>1997</OPTION>
<OPTION VALUE=1998>1998</OPTION>
<OPTION VALUE=1999>1999</OPTION>
<OPTION VALUE=2000>2000</OPTION>
<OPTION VALUE=2001>2001</OPTION>
<OPTION VALUE=2002>2002</OPTION>
<OPTION VALUE=2003>2003</OPTION>
<OPTION VALUE=2004>2004</OPTION>
<OPTION VALUE=2005>2005</OPTION>
<OPTION VALUE=2006 SELECTED>2006</OPTION>
<OPTION VALUE=2007>2007</OPTION>
<OPTION VALUE=2008>2008</OPTION>
<OPTION VALUE=2009>2009</OPTION>
<OPTION VALUE=2010>2010</OPTION>
</SELECT>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<INPUT TYPE="submit" VALUE="Get Date!">
</td>
</tr>
</FORM>

And this is a code I found that somehow related but I don't think it works on my case because the start_date and end_date of mine are not the same data type:

Start_Date = trim(start_month) & "/" & trim(start_day) & "/" & trim(start_year)
End_Date = trim(end_month) & "/" & trim(end_day) & "/" & trim(end_year)

"SELECT idCust " _
& "FROM customer " _
& "WHERE (((CDate(Format([DateAdded],'mm/dd/yyyy'))) Between CDate(Format([Start_Date],'mm/dd/yyyy')) And CDate(Format([End_Date],'mm/dd/yyyy'))))
 
Sort by date Sort by votes
Try changing that about a bit:

[tt]Start_Date = trim(start_year) & "/" & trim(start_month) & "/" & trim(start_day)
End_Date = trim(end_year) & "/" & trim(end_month) & "/" & trim(end_day)

"SELECT idCust " _
& "FROM customer " _
& "WHERE [DateAdded] Between #" & Start_Date _
& "# And #" & End_Date & "#"[/tt]

There is an ASP forum.
 
Upvote 0 Downvote
Thank you very much, Remou. I got that works now.

:)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bubba002
  • Locked
  • Question
WHERE clause in the Set command of an db.OpenRecordset method
Replies
16
Views
741
dhookom
dhookom
Jaime_78
  • Locked
  • Question
DoCmd.OpenReport Hangs when user wants to select location of report to save to
Replies
2
Views
246
Jaime_78
Jaime_78
VicM
  • Locked
  • Question
Where did my Access runtime go?
Replies
2
Views
131
VicM
VicM
TheresAlwaysAWay
  • Locked
  • Question
Need help finding where a number lies within a set of values 1
Replies
3
Views
174
TheresAlwaysAWay
TheresAlwaysAWay
Kalyan Ganesan
  • Locked
  • Question
Unable to insert thru a select
Replies
4
Views
160
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top