Link to home
Start Free TrialLog in
Avatar of Nmagsaysay
Nmagsaysay

asked on

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!



Avatar of CWS (haripriya)
CWS (haripriya)
Flag of India image

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
%>
Avatar of Nmagsaysay
Nmagsaysay

ASKER

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.

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
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!

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 & "'"
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?
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!

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?
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.)
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 !
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)

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??

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>
ASKER CERTIFIED SOLUTION
Avatar of CWS (haripriya)
CWS (haripriya)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello cyberwebservice,

Thank You!!

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

Thanks!

(Increased the points to 400)





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


The Order By DOES WORK!   PLEASE DISREGARD THE LAST COMMENT.
Hello Nmagsaysay,

Nice to hear that it works.

Also, Thanks for the points!