SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    london
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    689
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by webber123456; Jan 31, 2012 at 00:18. Reason: added info

  3. #3
    SitePoint Member
    Join Date
    Oct 2011
    Location
    Cape Coral, FL
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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... :-?

  4. #4
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your query is checking Date as well as Time. Use the function in both Sql Query and Date variable to remove the Time


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •