ASP - Displaying Access records between certain date ranges

The following code is based on one or two assumptions...
- The key field is a date/time field
- I didn't have any typo's
- The form dates are in the right format, this can be ensured via the MS calendar control (an htc).
<%
dte1 = request.form("dte1")
dte2 = request.form("dte2")
set db = createobject("adodb.connection")
set rs = createobject("adodb.recordset")
db.connectionstring = "blah"
db.open
strSQL = "SELECT * FROM blah WHERE (((blah.key)
strSQL = strSQL & " >#" & dte1 & "# And (blah.key)
strSQL = strSQL & " <#" & dte2 & "#));"
rs.open strsql, db
do until rs.eof
  response.write rs("blah")
  rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing
%>

mmm, maybe im doing summit wrong, this is my code:

<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<!--#include file="db.asp"-->


<%
Date1 = request.form("Date1")
Date2 = request.form("Date2")
set db = createobject("adodb.connection")
set rs = createobject("adodb.recordset")
db.connectionstring = "Date"
db.open
strSQL = "SELECT * FROM Date WHERE (((Date.key)
strSQL = strSQL & " >#" & Date1 & "# And (Date.key)
strSQL = strSQL & " <#" & Date2 & "#));"
rs.open strsql, db
do until rs.eof
 response.write rs("x_Date")
 rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing
%>



<%
response.buffer = true

' Open Connection to the database
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
%>






<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="get">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Date" value="LIKE,'%,%'">&nbsp;</font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Date %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Date %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Status &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Status" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="Radio" name="x_Status" value="Complete">Complete &nbsp; <input type="Radio" name="x_Status" value="In Progress">In Progress &nbsp;<input type="Radio" name="x_Status" value="Approved">Approved &nbsp;<input type="Radio" name="x_Status" value="Approved For Info Only">Approved For Info Only &nbsp;</font></font><p>
</td>
</tr>

<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Dealt By &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Dealt" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="text" name="x_Dealt" value="<%= x_Dealt %>" size=30 maxlength=60></font>&nbsp;</p>
</td>
</tr>



</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->

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

"Date" is the title of the field.

mmm, maybe im doing summit wrong, this is my code:

<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<!--#include file="db.asp"-->


<%
Date1 = request.form("Date1")
Date2 = request.form("Date2")
set db = createobject("adodb.connection")
set rs = createobject("adodb.recordset")
db.connectionstring = "Date"
db.open
strSQL = "SELECT * FROM Date WHERE (((Date.key)
strSQL = strSQL & " >#" & Date1 & "# And (Date.key)
strSQL = strSQL & " <#" & Date2 & "#));"
rs.open strsql, db
do until rs.eof
 response.write rs("x_Date")
 rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing
%>



<%
response.buffer = true

' Open Connection to the database
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
%>






<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="get">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Date" value="LIKE,'%,%'">&nbsp;</font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Date %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Date %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Status &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Status" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="Radio" name="x_Status" value="Complete">Complete &nbsp; <input type="Radio" name="x_Status" value="In Progress">In Progress &nbsp;<input type="Radio" name="x_Status" value="Approved">Approved &nbsp;<input type="Radio" name="x_Status" value="Approved For Info Only">Approved For Info Only &nbsp;</font></font><p>
</td>
</tr>

<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Dealt By &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Dealt" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="text" name="x_Dealt" value="<%= x_Dealt %>" size=30 maxlength=60></font>&nbsp;</p>
</td>
</tr>



</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->

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

"Date" is the title of the field.

You're missing a step -- I don't think it was in the original answer either.

Date1 = cdate(request.form("Date1"))
Date2 = cdate(request.form("Date2"))

Info from the form is a STRING, not a date. You have to convert it to a date type, or you can't compare them.

As long as it's a valid date string, it should convert to a valid date, and then your sql statement should work.

Actually you are adding the date fields to the sql string as strings, so converting them to date formats in a variant is not really required.  I'm assuming that you are not getting any data back since you havent mentioned an error message, if that is the case then the field names and table names look a bit suspect.  i don't usually work with access and to make sure the sql statement i was posting was correct i used access's query designer to create the statement.  perhaps use the designer to create the statement and then cut and past that into your code, or simply save it into the access database (if you have access to do that) and use it as you would a stored procedure in sql server.  if you could mention the actual error or problem that you are having it would narrow things down a bit.
btw the more i look at the sql statement the more i am sure that might be the problem.  access uses tablename.fieldname so if the table is called date and the field which contains the date is called key then everything is fine.  otherwise...

im getting this error.

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/os_psm/test2/changesstats.asp, line 19

strSQL = "SELECT * FROM Date WHERE (((Date.key)
-----------------------------------------------------^




The table that holds the infomation is called "changes"

Ooops seems i did have a typo, i forgot to close the string
just put a double quote at the end of the string to close the string constant.


strSQL = "SELECT * FROM Date WHERE (((Date.key)"
strSQL = strSQL & " >#" & Date1 & "# And (Date.key)"
strSQL = strSQL & " <#" & Date2 & "#));"

its stopped that error, but this one has appeared

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/os_psm/test2/changesstats.asp, line 30

line 30 been  'db.open'

you need to change the connection string to something like this
dbKids.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data\db2.mdb;"

or you can create a dsn via the odbc interface in the control panel (or in win 2000 control panel admin tools)
which points to the access database.  then the connection string would be "dsn=[dsnname]"

the first example is best since if the page is moved to another server you don't have to manually create a dsn on the server.  

thought so its this it?

' Open Connection to the database
set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

conn.Open xDb_Conn_Str
will work fine as long as the xDb_Conn_Str contains a valid connection string

im already opening the DB connection above this action, therefore i should really need to open it up again?

can you see whats wrong in my code?

The code LOOKS fine simply remove the second db con and open statement.  I'm assuming the page is recursive (calls itself) so you might want to play an if statement around the querying of the access db if the incoming form date fields are null strings.  If you don't you will probably get errors from the sql statement.

im getting this error now..

Unterminated string constant

/os_psm/test2/changesstats.asp, line 28

strSQL = "SELECT * FROM [changes] WHERE (((Date.key)
----------------------------------------------------^

And what's BEFORE that? You may well have the problem BEFORE that statement.

Robson8112,

Key is a reserved word in access... It is much better to change it... or you can enclosed with a bracket...
for reserved word in access try to check out the link...

http://www.btgi.net/reservedwords.cfm


Also you can use the SQL between...and operator to get the range of the specifiv date...
try to use this query...

strSQL = "SELECT * FROM Date WHERE [key] BETWEEN #" & Date1 & "# And #" & Date2 & "#;"
rs.open strsql, db

to learn more about sql between...and operator check out the link...

http://www.w3schools.com/sql/sql_between.asp


for example of a query try to check out the link...

http://www.w3schools.com/ado/ado_query.asp


HTH...

Happy programming...

Robson8112,

Another thing is in form action you use get and on the receiving you called the request collection by

request.form... which is incorrect cause using get method you need to use request.querystring...

Anyway below in the revise code...that you had posted...
KIndly change the name of the database base on your existing db.


=======
<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%
if request.form("action") = "Search" then

'kindly change the date.mdb base on your existing db.
set Cnxn=Server.CreateObject("ADODB.Connection")
Cnxn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnxn.Open(Server.Mappath("date.mdb"))

Date1 = request.form("Date1")
Date2 = request.form("Date2")


set rs = createobject("adodb.recordset")
strSQL = "SELECT * FROM Date WHERE [key] BETWEEN #" & Date1 & "# And #" & Date2 & "#;"
rs.open strsql, Cnxn

do until rs.eof
response.write rs("x_Date")
rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing

end if
%>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="POST">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Date" value="LIKE,'%,%'">&nbsp;</font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Date %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Date %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Status &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Status" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="Radio" name="x_Status" value="Complete">Complete &nbsp; <input type="Radio" name="x_Status" value="In Progress">In Progress &nbsp;<input type="Radio" name="x_Status" value="Approved">Approved &nbsp;<input type="Radio" name="x_Status" value="Approved For Info Only">Approved For Info Only &nbsp;</font></font><p>
</td>
</tr>

<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Dealt By &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Dealt" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="text" name="x_Dealt" value="<%= x_Dealt %>" size=30 maxlength=60></font>&nbsp;</p>
</td>
</tr>



</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->

HTH...

HAppy programming...

I've used the code supplied above (thanks gladxml) , im not getting any errors now, however it doesnt display the records in the date range that i requestm instead it shows all the records in the the table.

does the date field need to be the Primary key? as i have a ID field which is currently primary?

Robson8112,

>>>>
does the date field need to be the Primary key?
as i have a ID field which is currently primary?
>>>>

If you want to get the date on that particular user you must include the ID on your where clause...

To debug double check your database and the value of the Date1 and  Date2 input fields. or you can do a response.write on your sql statement...

HTH...

HAppy programming...

Why don't you try this... and remember, date is a reserved word. I'd change that name. That IS the table name, right?

SELECT * FROM Date WHERE ([key]>#" & Date1 & "# And [key]<#" & Date2 & "#)"

If you use BETWEEN, you WILL get every record that falls BETWEEN the dates -- NOT where the date is GREATER THAN date1 and LESS THAN date2.

AND -- is KEY the date? Isn't that the primary key?

its still displaying all the records..

The ID is just a random number used by access and the ID is the primary key.

The table name is called 'changes', but i've edited the code to reflect this.

Webwoman,

The field title is Date, should key = date?

help!

I dont think the script is reading in the values of Date1 and Date2 on the form

Yes. And change the name because DATE is a reserved word. The dates you're checking have to be DATES, not STRINGS. Are they passed from the form? If so, you need to change them to dates, because forms pass STRINGS, not DATES.

The structure for the SQL statement goes like this...
SELECT * FROM Tablename WHERE (table_date_field>#" & date_from_form1 & "# And table_date_field<#" & date_from_form2 & "#)"

Where date_from_form1 is the EARLIER date and date_from_form2 is the LATER date. I use the parens to make it easier to see where the quotes at the end go, and to group what I'm checking against -- it's not necessary, but it makes it easier for me to debug.

You also need to do this with the form data passed...

date_from_form1=cdate(request("formdate1"))
date_from_form2=cdate(request("formdate2"))
Otherwise it's just a string, not a date.

Im only a amatur at ASP.

I input the date on the form, ie 19/03/2003

so i guess these are passed from the form.

im not sure if there strings or dates

Strings. Everything sent from a form is a string. Also, Access dates are in mm/dd/yy format, not dd/mm/yy format. You'll need to swap that around to get it to work.

in my access tables, the date is shown as dd/mm/yy, field is set to short date

if the date input on the form is a string, how do i change it to read dates ????

cdate(string) converts it to a date. It will need to be in mm/dd/yy format (I think). Access stores the dates in date fields as a date/time value, and it doesn't understand short date, long date, time formats, etc. You'll have to format the date if you want anything other than mm/dd/yyyy format.

i've added the cdate(string).

for example, when i search for from 03/03/2003 to 03/03/2003, which should show records on that date, however, it still shows all records on the table.  Im i missing something?

here my code:

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

 if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%


if request.form("action") = "Search" then


set Cnxn=Server.CreateObject("ADODB.Connection")
Cnxn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnxn.Open(Server.Mappath("db.mdb"))

Date1 = cdate(request.form("Date1"))
Date2 = cdate(request.form("Date2"))

set rs = createobject("adodb.recordset")
strSQL = "SELECT * FROM changes WHERE ([Date]>#" & Date1 & "# And [Date]<#" & Date2 & "#)"
rs.open strsql, Cnxn

do until rs.eof
response.write rs("x_Date")
rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing

end if
%>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="POST">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Date" value="LIKE,'%,%'">&nbsp;</font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Date %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Date %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Status &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Status" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="Radio" name="x_Status" value="Complete">Complete &nbsp; <input type="Radio" name="x_Status" value="In Progress">In Progress &nbsp;<input type="Radio" name="x_Status" value="Approved">Approved &nbsp;<input type="Radio" name="x_Status" value="Approved For Info Only">Approved For Info Only &nbsp;</font></font><p>
</td>
</tr>


<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Dealt By &nbsp;</font></font></p></td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
<input type="hidden" name="z_Dealt" value="LIKE,'%,%'">&nbsp;</font></p>
</td><td bgcolor="#F5F5F5"><p align="center"><font face="Arial" size="2"><input type="text" name="x_Dealt" value="<%= x_Dealt %>" size=30 maxlength=60></font>&nbsp;</p>
</td>
</tr>



</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->

Change any field named DATE -- it's a reserved word.

ok, i've changed all fields named DATE to DATES, which hopefully isnt a reserved word. I've also changed the field name in the table to DATES.

I've tried my code and it still doesnt work.

is there something wrong with my form?

Help!

Don't know... you haven't shown us the form. ;-)

If you're using GET on it, it's not going to work if you're using request.FORM(formfield) on the ASP page. Request.FORM requires you use POST.

Change it to just request(formfield), that works for both GET and POST.

im using GET, so changed it the correct above

here my code:

<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%


if request.form("action") = "Search" then


set Cnxn=Server.CreateObject("ADODB.Connection")
Cnxn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnxn.Open(Server.Mappath("db.mdb"))

Date1 = cdate(request("Date1"))
Date2 = cdate(request("Date2"))



set rs = createobject("adodb.recordset")
strSQL = "SELECT * FROM changes WHERE ([Date]>#" & Date1 & "# And [Date]<#" & Date2 & "#)"
rs.open strsql, Cnxn

do until rs.eof
response.write rs("x_Dates")
rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing

end if
%>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>

------- below is the form ---


<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="GET">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Dates %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Dates %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>


</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->

as u can see the values that are entered are passed to another ASP page, which displays the result/search

action="changeslist.asp"

im i doing this right?

(thanks for your patience by the way :-) )

no one can help?

It's right if what you've shown us is the changeslist.asp page. If it's NOT, that's where the problem is. ;-)

mmmm, the page i've shown u is 'changestats.asp'

'changeslist.asp' is where my records/results from search are displayed.

I've got a another search page, which is very similar, but without the date range, it works fine... :-(

Robson8112,

Sorry have encountered some probelm with my connection...

Ok lets look at the SQL between and operator

try to replace the slq line with this

strSQL = "SELECT * FROM Date WHERE [Date] BETWEEN #" & Date1 & "# And #" & Date2 &"#;"
response.write strsql
response.end

Now check the result and compare the value of the date printed on the browser with your...

If it is possible try to post it here... bothe data...

HTH...

HAppy programming...

Ok,  i've tried that, but still no joy, it seems that it doesnt pass the value to x_Dates, which is then fed to my other asp page that displays the results (if u see what i mean) anyway, here my code


---------->>>>>>

<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%


if request.form("action") = "Search" then


set Cnxn=Server.CreateObject("ADODB.Connection")
Cnxn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnxn.Open(Server.Mappath("db.mdb"))

Date1 = cdate(request("Date1"))
Date2 = cdate(request("Date2"))



set rs = createobject("adodb.recordset")
strSQL = "SELECT * FROM changes WHERE [Dates] BETWEEN #" & Date1 & "# And #" & Date2 &"#;"
response.write strsql
response.end


do until rs.eof
response.write rs("x_Dates")
rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing

end if
%>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="changeslist.asp" method="GET">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">



<tr>
<td bgcolor="#330066">
<p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date &nbsp;</font></font></p>
</td>
<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">&nbsp;
</td>

<td bgcolor="#F5F5F5">
<p align="center"><font face="Arial" size="2">From &nbsp;<input type="text" name="Date1" value="<%= x_Dates %>" size=14 maxlength=10>&nbsp; To &nbsp;<input type="text" name="Date2" value="<%= x_Dates %>" size=14 maxlength=10></font>&nbsp;</p>
</td>
</tr>






</table>
<p align="left">
<input type="submit" name="Action" value="Search">
</form>

<!--#include file="footer.asp"-->



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

as u can see, i've changed the code u gave me to match my naming in the database (ie, Changes is the table name, Dates in the DATE field)

Robson8112,

Could you please run this code... adn check the result...

==just cut and paste
<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%


if request.form("action") = "Search" then


set Cnxn=Server.CreateObject("ADODB.Connection")
Cnxn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnxn.Open(Server.Mappath("db.mdb"))

Date1 = cdate(request("Date1"))
Date2 = cdate(request("Date2"))

set rs = createobject("adodb.recordset")
strSQL = "SELECT * FROM changes WHERE [Dates] BETWEEN #" & Date1 & "# And #" & Date2 &"#;"
response.write strsql
response.end
'unremark this if the strsql string is correct...
'rs.open strsql, Cnxn

do until rs.eof
response.write rs("x_Dates")
rs.movenext
loop
rs.close
db.close
set rs = nothing
set db = nothing

end if
%>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="<%=Request.ServerVariables("SCRIPT_NAME")%>" method="GET">
<p align="center">

  <table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">
    <tr>
      <td bgcolor="#330066">
        <p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date
          &nbsp;</font></font></p>
      </td>
      <td bgcolor="#F5F5F5">
        <p align="center"><font face="Arial" size="2">From &nbsp;
          <input type="text" name="Date1" size=14 maxlength=10>
          &nbsp; To &nbsp;
          <input type="text" name="Date2" size=14 maxlength=10>
          </font>&nbsp;</p>
      </td>
      <td bgcolor="#F5F5F5">
        <input type="submit" name="action" value="Search">
      </td>
    </tr>
  </table>
</form>

====

Please post the result of the sql string... and the sample date from the db...

HTH...

HAppy programming...

ok, i used the date range 03/03/03 to 03/03/03, which ideally should just show that date, i also tried 03/04/03 to 03/05/03.  nothing appears, it clears the input boxes on the form.  anyway, heres the HTML output

----------->


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="/os_psm/test2/changesstats.asp" method="GET">
<p align="center">

 <table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">
   <tr>
     <td bgcolor="#330066">
       <p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date
         &nbsp;</font></font></p>
     </td>
     <td bgcolor="#F5F5F5">
       <p align="center"><font face="Arial" size="2">From &nbsp;
         <input type="text" name="Date1" size=14 maxlength=10>
         &nbsp; To &nbsp;
         <input type="text" name="Date2" size=14 maxlength=10>
         </font>&nbsp;</p>
     </td>
     <td bgcolor="#F5F5F5">
       <input type="submit" name="action" value="Search">
     </td>
   </tr>
 </table>
</form>

can nobody help? surely someone has does this before?

Robson8112,

If it is not a probelm to youm, post you email address and I will to have a look in your db as well as your script... Actually all the help that you can get is already posted by some of the expert but still did not resolve the problem... Also run and test the code here...

I really like to help you...

Regards,
gladxml

Hi,

heres my e-mail: alexander.robson@unn.ac.uk

please e-mail me, and i will send u a zipped up version of my db and asp pages.

Thanks

Hi,

heres my e-mail: alexander.robson@unn.ac.uk

please e-mail me, and i will send u a zipped up version of my db and asp pages.

Thanks

Hi,

heres my e-mail: alexander.robson@unn.ac.uk

please e-mail me, and i will send u a zipped up version of my db and asp pages.

Thanks

Robson8112,

Ooops I forgot to add this line on top...

<% if Session("cwnaosat_status") <> "login" then response.redirect "login.asp" %>

HAppy programming...

Regards,
gladxml

hhhmm, it doesnt display properly. it just shows dates, not the records.


this is the HTML output, when seaching for 03/03/03 - 03/04/03.

--->>>

03/03/03<br>04/03/03<br>04/03/03<br>03/03/03<br>03/03/03<br>03/03/03<br>03/03/03<br>03/03/03<br>04/03/03<br>03/03/03<br>03/03/03<br>03/03/03<br>04/03/03<br>04/03/03<br>04/03/03<br>04/03/03<br>03/03/03<br>


<p align="center"><u><b><font face="Arial" size="3" color="#000080">Change
Statistics</font></b></u></p>

<script language="JavaScript" src="ew.js"></script>
<script language="JavaScript">
<!-- start Javascript
function  EW_checkMyForm(EW_this) {
return true;
}
// end JavaScript -->
</script>
<form onSubmit="return EW_checkMyForm(this);"  action="/os_psm/test2/changesstats.asp" method="POST">
<p align="center">

<table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">
  <tr>
    <td bgcolor="#330066">
      <p align="center"><font color="#FFFFFF"><font face="Arial" size="2">Date
        &nbsp;</font></font></p>
    </td>
    <td bgcolor="#F5F5F5">
      <p align="center"><font face="Arial" size="2">From &nbsp;
        <input type="text" name="Date1" size=14 maxlength=10>
        &nbsp; To &nbsp;
        <input type="text" name="Date2" size=14 maxlength=10>
        </font>&nbsp;</p>
    </td>
    <td bgcolor="#F5F5F5">
      <input type="submit" name="action" value="Search">
    </td>
  </tr>
</table>
</form>

Robson8112,

Just try to change this lines regarding the data that you want to display in your db...

response.write rs("Dates")  & "<br>"

I just make a test since you dont want any of your fieldname to be viewed...

BTW what kind of output you want to achieve...

HTH...

HAppy programming....

Glad,

From my db i sent you, look at the changesrch.asp page.  Do a little search, and see the output. Something like that if possible.

also, i've just realised theres a confusion with UK and US date format,

i.e, search for 03/03/03 - 08/03/03 and it display loads of records.  Id there a way around this?

Thanks

ps, i dont mind u showing my field names, its just the data.

Glad,

Thanks for your time

The points are yours :-)