Solved

Select Date Range Based on Variable

Posted on 2006-11-18
18
199 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
  • 10
  • 8
18 Comments
 
LVL 16

Expert Comment

by:CWS (haripriya)
Comment Utility
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
Comment Utility
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)
Comment Utility
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
 

Author Comment

by:Nmagsaysay
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:CWS (haripriya)
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


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

0
 

Author Comment

by:Nmagsaysay
Comment Utility

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

Expert Comment

by:CWS (haripriya)
Comment Utility
Hello Nmagsaysay,

Nice to hear that it works.

Also, Thanks for the points!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now