Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select Date Range Based on Variable

Posted on 2006-11-18
18
Medium Priority
?
232 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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

722 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