Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Select Date Range Based on Variable

Using Dreamweaver, I used the Recordset(Query) to select a date range between 'start' and 'stop.'  The use of the variable for the dates is not recognized by the query.  I want the user to enter the date range by entering in a textbox the beginning date as mm/dd/yy (variable 'start') and a ending date as  mm/dd/yy (variable 'stop').  I declared the textboxes into session variables 'start' and 'stop' so that the values can be used on other pages.


Here are the codes:

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conDHDB_STRING
Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN '" + Replace(Recordset1__START, "'", "''") + " '  AND  ' " + Replace(Recordset1__STOP, "'", "''") + "'  "
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>


<%
Dim Recordset1__START
Recordset1__START = "9/1/2006"
If (Session("field1")  <> "") Then
  Recordset1__START = Session("field1")
End If
%>
<%
Dim Recordset1__STOP
Recordset1__STOP = "11/1/2006"
If (Session("field2") <> "") Then
  Recordset1__STOP = Session("field2")
End If
%>

This code does not generate anything!  What am I missing and/or how can I better achieve a date range selection from an access database where the user makes the date range selection?

Thanks!



0
Nmagsaysay
Asked:
Nmagsaysay
  • 10
  • 8
1 Solution
 
CWS (haripriya)Commented:
Well. Give a response.write statement and see,

Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN '" + Replace(Recordset1__START, "'", "''") + " '  AND  ' " + Replace(Recordset1__STOP, "'", "''") + "'  "
Response.write "Start Date = " & Recordset1__Start  & " and  End date = " & Recordset1__STOP

If it doesnot display the values of start and end date then, you should put the below code at the beginning followed by your select query.

<%
Dim Recordset1__START
Recordset1__START = "9/1/2006"
If (Session("field1")  <> "") Then
  Recordset1__START = Session("field1")
End If
%>
<%
Dim Recordset1__STOP
Recordset1__STOP = "11/1/2006"
If (Session("field2") <> "") Then
  Recordset1__STOP = Session("field2")
End If
%>
0
 
NmagsaysayAuthor Commented:
Both suggestions do not work.

I placed Response.write "Start Date = " & Recordset1__Start  & " and  End date = " & Recordset1__STOP after the Recordset1.Source line. Since the start date and the end date are variables which holds the users respronse, I changed "Start Date" to the variable "Start" and "End date" to the varible "End"

I need the Select statement to recognize the variable "Start" and  variable "End" (which hold the users respronse as to where to start and end the date range.)

The user selects the date range.

0
 
CWS (haripriya)Commented:
From where did you get these dates Recordset1__Start  and Recordset1__STOP ?

If you are getting this from the form then you should call like this,

Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN '" + Replace(Request.Form("Recordset1__START"), "'", "''") + " '  AND  ' " + Replace(Request.Form("Recordset1__STOP"), "'", "''") + "'  "



Also, posting the full code will be helpful in solving this issue
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
NmagsaysayAuthor Commented:
Page 1: A form for the user to enter starting date and ending date of query range.  (Starting Date =  textfield1  ,  Ending Date = textfield2 )    

Page 2: <% Session("field1") = Request.Form("textfield1")  %>
            <% Session("field2") = Request.Form("textfield2") %>

In the recordset:  SELECT *
                          FROM DH
                          WHERE SNDATE BETWEEN  START   AND   STOP
Variables:         Names      Default Value    Run-Time Value
                         Start        9/1/2006         Session("field1")
                         Stop        11/1/2006        Session("field2")


The Codes Generated previously are in the below order in program:


<%
Dim Recordset1__START
Recordset1__START = "9/1/2006"
If (Session("field1")  <> "") Then
  Recordset1__START = Session("field1")
End If
%>
<%
Dim Recordset1__STOP
Recordset1__STOP = "11/1/2006"
If (Session("field2") <> "") Then
  Recordset1__STOP = Session("field2")
End If
%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conDHDB_STRING
Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN '" + Replace(Recordset1__START, "'", "''") + " '  AND  ' " + Replace(Recordset1__STOP, "'", "''") + "'  "
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

Dreamweaver generates the code: Recordset1__START  and   Recordset1__STOP

When I run the pages and enter a date in textbox 1 (textfield1) ex. 8/1/2006   and textbox 2 (textfield2)  ex. 11/1/2006  NO range or any errors are generated.

Thanks for your help!

0
 
CWS (haripriya)Commented:
Since the data type is date there is no need for the Replace function.

Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN '" & Recordset1__START & "'  AND  '" & Recordset1__STOP & "'"
0
 
NmagsaysayAuthor Commented:
The default of the Start and Stop textfield has a value of Date()

When I change the Start Date in textfield1 (Start) to 9/23/2006  and the  Ending Date textfield2 (Stop) 11/23/2006 I get a date range:

11/26/2005
12/26/2005
5/5/2006
2/1/2006
3/9/2006

I am getting a range but the date ranges are off.

How do I fix?
0
 
NmagsaysayAuthor Commented:
I thought about your statement: "Since the data type is date there is no need for the Replace function."

If you reference the access data base field, I changed the data type field to "text"

When I change the data type to "Date/Time" using the same coding, I get an error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


Hope this helps!

0
 
CWS (haripriya)Commented:
Hello Nmagsaysay,

There is no need to change the data type. You are getting the results but the results are not proper. Try this.

Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN #" & Recordset1__START & "#  AND  #" & Recordset1__STOP & "#"

Also, I am not sure of anything without seeing the complete code and the database, if you need this to be solved urgently you can always zip all your files including the database and send it to me: india<underscore>priyam<at>yahoo<dot>co<dot>in

So that we can solve it quickly and post the results here.
Is that okay with you?
0
 
NmagsaysayAuthor Commented:
I fixed a problem with the database and the date range sequence improved.  When I do a range with the month of 1 (January,) I get all ranges that start with the number "1." (1/  /2005, 10/  /2005, 11/  /2005, 12/  /2005,, 1/  /2006 (both 2005 and 2006).

The date range is almost there.  How do I make the range recognize the month 1/ (January) as different from 10/ 11/ 12/ ?


(The database and attached files contain proprietary information.  It may not be necessary to take the required time it would take to remove the proprietary data if there is a solution to the select deciphering 1 from 10, 11 and 12.)
0
 
CWS (haripriya)Commented:
Hello Nmagsaysay,

If it is taking all the month beginning with 1 then, it is not treated as a DATE.
Make sure the data type is "DATE" in the database.

Because I am very sure that the MS Access database will not accept "Between ' and ' " for date. It will accept only "# and # " for the DATE data type. Like this,

Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN #" & Recordset1__START & "#  AND  #" & Recordset1__STOP & "#"

Good Luck !
0
 
NmagsaysayAuthor Commented:
Hello cyberwebservice

When I change the access database to Date/Time (short date) using the ' "&  I get the error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

When I change the code to #" &  I get a range of dates but not the dates entered into the entered textboxes.


Note: (Using the text field in access database with ' "&, all other date ranges are perfect.  Only problem with 1's)

0
 
NmagsaysayAuthor Commented:
I still haven't resolved:  Selecting a Date Range Based on Variable

It appears that the value in the textboxes (textfield1, textfield2) are not recognized as a date.  I added the below code to the input field:

  <p align="center" ><span class="style3">Starting Date :
     <input name="textfield1" type="text" value="<%= formatDatetime("datefield",vbshortDate)  %> " >
          </span></p>
          <p align="center" ><span class="style3">  Ending Date:</span>
            <input name="textfield2" type="text" value="<%= formatDatetime("datefield",vbshortDate)  %> ">           
            <br>
          </p>


But I get this error message:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'formatDatetime'



How do I get the Access database date/time, short date field to recognize the textbox value as a date??

0
 
CWS (haripriya)Commented:
GIve this:

<p align="center" ><span class="style3">Starting Date :
     <input name="textfield1" type="text" value="<%= formatDatetime(datefield)  %> " >
          </span></p>
          <p align="center" ><span class="style3">  Ending Date:</span>
            <input name="textfield2" type="text" value="<%= formatDatetime(datefield)  %> ">           
            <br>
          </p>
0
 
CWS (haripriya)Commented:
I just tell you an example of how to do handle the date textboxes.

Access Database:
Field           Data Type
---------------------------
SNDATE      DATE/TIME

Input Form:
-------------
<form id="form1" name="form1" method="post" action="next.asp">
  <label>Start Date
  <input name="textfield1" type="text" id="textfield1" />
  </label>
  <p><br />
    <label>End Date
    <input name="textfield2" type="text" id="textfield2" />
    </label>
  </p>
<input type="submit" name="Submit" value="OK" />
</form>

next.asp:
-----------

Dim sDate, eDate
sDate = Request.Form("textfield1")
eDate = Request.Form("textfield2")
session("startdate") = sDate
session("enddate") = eDate

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conDHDB_STRING
Recordset1.Source = "SELECT *  FROM DH WHERE SNDATE BETWEEN #" & sDate & "# and #" & edate & "#"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

---------------------------------------
---------------------------------------
---------------------------------------

0
 
NmagsaysayAuthor Commented:
Hello cyberwebservice,

Thank You!!

Your suggestings worked.  The select is recognizing form variable dates 2005 from 2006.

Thanks!

(Increased the points to 400)



0
 
NmagsaysayAuthor Commented:


 ORDER BY does not work with your Select statement.  How can I order the selected dates by Contacts?

0
 
NmagsaysayAuthor Commented:

The Order By DOES WORK!   PLEASE DISREGARD THE LAST COMMENT.
0
 
CWS (haripriya)Commented:
Hello Nmagsaysay,

Nice to hear that it works.

Also, Thanks for the points!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now