Hi,

I have an ASP page that enables the user to search an SQL database.

I have a form that lets the user select a number of different criteria to search on. One of the form elements is a Select box that contains the 12 months, their values being 1-12 respectively.

I can search with this and successfully return only the records containing the selected month. However, I would like to be able to search on all months. I added another option "All", with a null value, but this does not work.

My SQL statement looks something like ... SELECT * FROM myTable WHERE Month(myDateField) = 'myDateVariable'

I have tried using WHERE Month(myDateField) LIKE '%myDateVariable%' but when searching on January (1) and February(2), other months that contain these digits are also returned (Oct, Nov, Dec).

Is it possible to set the values of my select options to text 'Jan', 'Feb', etc? Or is there another way?

s

What type is the MyDateField?
Try using DATEPART() instead of MONTH() there.

_________________________
- Vijay G
Strive for Perfection

Hi happygv,

The type is datetime. I tried using DATEPART(mm, myDateField), but still no luck.

I also tried DATEPARAT(m, myDateField) and DATEPART(month, myDateField). I have also tried changing the format of the field to Medium, Short etc.

This is part of my select code:

<select name="selectMonth" id="selectMonth">
<option value="">All</option>
<option value="1">Jan</option>
<option value="2">Feb</option>
<option value="3">Mar</option>
</select>

I have tried various combinations of values and different SQL statements that include either LIKE %selectMonthVariable% or = selectMonthVariable.

Using = I can get it to work, but not for the 'All' select option. I have also tried setting the option values to "01" for January and "02" for Feb, but this doesn't work either. I thought you might be able to set the values to 'Jan', 'Feb' etc, but this doesn't work.

This is starting to drive me nuts! There has got to be an easy way to do this.

Thanks
s

Maybe try:
1..Change your All value to <option value="0">All Months</option>
2..Now when the form gets submitted conditionaly create the sql, EG:

sql = "SELECT * FROM myTable WHERE "
if request.form("monthSelections") = 0 then
   sql = sql & " Month(myDateField)<= 12;"
else
   sql = sql & " Month(myDateField)= 'myDateVariable';"
end if

Wind is your friend
Matt

Thanks Matt, I thought I might end up having to do something like that.

It just seems strange that I can't use text values 'Jan', 'Feb', 'Mar' etc. I have seen various examples that show you can query datetime fields in this manner.

Yes, got it to work using conditional statement, so thanks again Matt.

no worries

;;;It just seems strange that I can't use text values 'Jan', 'Feb', 'Mar' etc
You can but Integers are generaly faster than strings, use strings if you can not use integers.

Wind is your friend
Matt