Solved

Select Date Range Based on Variable

Posted on 2006-11-18
18
226 Views
Last Modified: 2010-04-25
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
Comment
Question by:Nmagsaysay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17977477
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
 

Author Comment

by:Nmagsaysay
ID: 17985257
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17985326
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Nmagsaysay
ID: 17988744
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17993363
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
 

Author Comment

by:Nmagsaysay
ID: 18004496
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
 

Author Comment

by:Nmagsaysay
ID: 18004548
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 18005998
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
 

Author Comment

by:Nmagsaysay
ID: 18010359
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 18010465
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
 

Author Comment

by:Nmagsaysay
ID: 18010494
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
 

Author Comment

by:Nmagsaysay
ID: 18021475
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
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 18025643
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
 
LVL 16

Accepted Solution

by:
CWS (haripriya) earned 400 total points
ID: 18026697
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
 

Author Comment

by:Nmagsaysay
ID: 18029130
Hello cyberwebservice,

Thank You!!

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

Thanks!

(Increased the points to 400)



0
 

Author Comment

by:Nmagsaysay
ID: 18029330


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

0
 

Author Comment

by:Nmagsaysay
ID: 18029391

The Order By DOES WORK!   PLEASE DISREGARD THE LAST COMMENT.
0
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 18043165
Hello Nmagsaysay,

Nice to hear that it works.

Also, Thanks for the points!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question