SQL Statement with Date Format

[Brachole]

Hi,

the field in my Access DB is a date field (1/25/2006). My HTML Form uses 2 drop down fields for this. One is the Month, the other is the Year.

So query would call everything from a selected Month and Year. Example: I need to query everything from January of 2006. So my SQL is:

SELECT *
FROM RMA_Number
WHERE LEFT(CalibrationDueDate,1) = 1 And RIGHT(CalibrationDueDate,2) = 2006

That work great, except for the Months that use a double digit (October, November and December). So for those months, I would need to use:
WHERE LEFT(CalibrationDueDate,2) = 10 And RIGHT(CalibrationDueDate,2) = 2006

How can I fix that for Dynamic Queries....

I have attached my HTML Form so you can see what I mean.

<form action="CalibrationDueDate_Result.asp" method="get" name="RMARequest" id="RMARequest">
<table width="100%" border="0">
<tr>
<td colspan="3" class="GeneralContentBold">Select a Month</td>
<td width="21%" class="GeneralContentBold">Select a Year</td>
<td width="28%">&nbsp;</td>
</tr>
<tr>
<td colspan="3" class="VolumeDiscountGSA"> <select name="Month" id="Month">
<option selected>Select One -----------&gt;</option>
<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>
<br>
</td>
<td><select name="Year" id="Year">
<option selected>Select One ---------&gt;</option>
<option value="8">2008</option>
<option value="7">2007</option>
<option value="6">2006</option>
<option value="5">2005</option>
<option value="4">2004</option>
<option value="3">2003</option>
<option value="2">2002</option>
<option value="1">2001</option>
</select></td>
<td>&nbsp; </td>
</tr>
<tr>
<td height="40" colspan="5"><div align="center">
<p class="horizontalgreyline">&nbsp;</p>
</div></td>
</tr>
<tr>
<td colspan="3" class="GeneralContentBold">Sort By</td>
<td class="GeneralContentBold">Results per page</td>
<td>&nbsp;</td>
</tr>
<tr>
<td width="21%"><select name="Sort" id="Sort">
<option value="CompanyName">Company Name</option>
<option value="ContactName">Contact Name</option>
<option value="CalibrationDueDate">Calibration Due Date</option>
</select></td>
<td width="14%" class="GeneralContent">
<label>
<input name="selectorder" type="radio" value="ASC" checked>
Ascending</label></td>
<td width="16%" class="GeneralContent">
<label>
<input type="radio" name="selectorder" value="DESC">
Descending</label></td>
<td><select name="Results" id="select">
<option value="25" selected>25</option>
<option value="50">50</option>
<option value="100">100</option>
<option value="200">200</option>
</select></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="40" colspan="5" class="horizontalgreyline">&nbsp;</td>
</tr>
<tr>
<td colspan="3"><input type="submit" name="Submit" value="Search"></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>

[russell]

WHERE DatePart(m, CalibrationDueDate) = 1
And DatePart(yyyy, CalibrationDueDate) = 2006

[Brachole]

tried it, but keep getting the following error:

Too few parameters. Expected 2.

/RMANumber/CalibrationDueDate_Result.asp, line 50

<%
var rsAllRMA__Month = "1";
if (String(Request.QueryString("Month")) != "undefined" &&
String(Request.QueryString("Month")) != "") {
rsAllRMA__Month = String(Request.QueryString("Month"));
}
%>
<%
var rsAllRMA__Year = "1";
if (String(Request.QueryString("Year")) != "undefined" &&
String(Request.QueryString("Year")) != "") {
rsAllRMA__Year = String(Request.QueryString("Year"));
}
%>
<%
var rsAllRMA__Sort = "1";
if (String(Request.QueryString("Sort")) != "undefined" &&
String(Request.QueryString("Sort")) != "") {
rsAllRMA__Sort = String(Request.QueryString("Sort"));
}
%>
<%
var rsAllRMA__SelectOrder = "1";
if (String(Request.QueryString("selectorder")) != "undefined" &&
String(Request.QueryString("selectorder")) != "") {
rsAllRMA__SelectOrder = String(Request.QueryString("selectorder"));
}
%>
<%
var rsAllRMA = Server.CreateObject("ADODB.Recordset");
rsAllRMA.ActiveConnection = MM_connDataSource2_STRING;
rsAllRMA.Source = "SELECT * FROM RMA_Number WHERE DatePart(m, CalibrationDueDate) = "+ rsAllRMA__Month.replace(/'/g, "''") + " And DatePart(yyyy, CalibrationDueDate) = "+ rsAllRMA__Year.replace(/'/g, "''") + " ORDER BY "+ rsAllRMA__Sort.replace(/'/g, "''") + " "+ rsAllRMA__SelectOrder.replace(/'/g, "''") + "";
rsAllRMA.CursorType = 0;
rsAllRMA.CursorLocation = 2;
rsAllRMA.LockType = 1;
rsAllRMA.Open();
var rsAllRMA_numRows = 0;
%>

Line 50 is the rsAllRMA.Open();

[Brachole]

got it...

WHERE DatePart('m', CalibrationDueDate) = 1
And DatePart('yyyy', CalibrationDueDate) = 2006

added single quotes around m and yyyy

[russell]

cool. my bad. sql server no quotes around the constants, access quoted -- as u figured out :)