Searching by Date

I have a script that works and returns results if the date selected is "Before" or "After" but if I select "ON" I always get no results when I should be.

This is the search code:

Code:

---

Scheduled:
        <select style="display:inline" name="period" id="period">
        <option value="">Select Period</option>
          <option value="ON">ON</option>
          <option value="BEFORE">BEFORE</option>
          <option value="AFTER">AFTER</option>
          </select>
          <select style="display:inline" name="pday" id="pday">
        <%
                for i = 1 to 31
                %>
          <option value="<%=i%>"
          <%
                  if day(date) = i then
                  response.write "selected"
                  end if
                  %>><%=i%></option>
        <%
                next
                %>
            </select>
            <select style="display:inline" name="pmonth" id="pmonth">
        <%
                for i = 1 to 12
                %>
          <option value="<%=i%>"
          <%
                  if month(date) = i then
                  response.write "selected"
                  end if
                  %>
          ><%=left(monthname(i),3)%></option>
        <%
                next
                %>
        </select>
        <select style="display:inline" name="pyear" id="pyear">
    <%
                for i = 2005 to 2025
                %>
          <option value="<%=i%>"
          <%
                  if year(date) = i then
                  response.write "selected"
                  end if
                  %>
          ><%=i%></option>
        <%
                next
                %>
    </select>

---

The following gives me results for 'period=after' or 'period=before' except if 'period=ON' for some reason, when there should be results.

Code:

---

        if period <> "" then
                if period = "ON" then
                SQL7 = " AND date_of_exam = #" & pday & "-" & pmonth & "-" & pyear & "#"
                end if
                if period = "BEFORE" then
                SQL7 = " AND date_of_exam < #" & pday & "-" & pmonth & "-" & pyear & "#"
                end if
                if period = "AFTER" then
                SQL7 = " AND date_of_exam > #" & pday & "-" & pmonth & "-" & pyear & "#"
                end if
        end if

---

Now, the date_of_exam is stored in Access as a General date so not sure if ON is looking to the minute? Any ideas?

odd that 2 out 3 should work.

try outputting "date_of_exam" value and the value of "pday & "-" & pmonth & "-" & pyear"

to see exactly what the computer is comparing.

You may need to apply cDate function to one or both.

I am going to try this and will let you know about my experience. I am not a good developer but I can test many things and reuse code easily. I don't know how... :-?

Your query is checking Date as well as Time. Use the function in both Sql Query and Date variable to remove the Time