?
Solved

ASP - Displaying Access records between certain date ranges

Posted on 2003-03-12
55
Medium Priority
?
190 Views
Last Modified: 2010-08-05
Hi,

I have a Access database which is displayed via ASP.  I have one table with various colums, one of them colums been the date (dd/mm/yyyy - UK format)  

What i need is a ASP page/code which lets me input the date range (from and to) and displys the records that match in that range.

Can anyone help?

0
Comment
Question by:Robson8112
[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
  • 31
  • 9
  • 9
  • +1
55 Comments
 
LVL 11

Expert Comment

by:LordWabbit
ID: 8120340
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
%>

0
 

Author Comment

by:Robson8112
ID: 8120475
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.
0
 

Author Comment

by:Robson8112
ID: 8120507
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.
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
LVL 19

Expert Comment

by:webwoman
ID: 8121090
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.
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 8125575
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...
0
 

Author Comment

by:Robson8112
ID: 8126389
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"
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 8126456
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 & "#));"
0
 

Author Comment

by:Robson8112
ID: 8126473
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
0
 

Author Comment

by:Robson8112
ID: 8126485
line 30 been  'db.open'
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 8126854
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.  
0
 

Author Comment

by:Robson8112
ID: 8126874
thought so its this it?

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

Expert Comment

by:LordWabbit
ID: 8126942
conn.Open xDb_Conn_Str
will work fine as long as the xDb_Conn_Str contains a valid connection string
0
 

Author Comment

by:Robson8112
ID: 8127418
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?
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 8134328
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.
0
 

Author Comment

by:Robson8112
ID: 8135131
im getting this error now..

Unterminated string constant

/os_psm/test2/changesstats.asp, line 28

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

Expert Comment

by:webwoman
ID: 8140548
And what's BEFORE that? You may well have the problem BEFORE that statement.
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8141830
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...


0
 
LVL 15

Expert Comment

by:gladxml
ID: 8141855
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...
0
 

Author Comment

by:Robson8112
ID: 8150685
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.
0
 

Author Comment

by:Robson8112
ID: 8150720
does the date field need to be the Primary key? as i have a ID field which is currently primary?
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8155968
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...

0
 
LVL 19

Expert Comment

by:webwoman
ID: 8156534
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?
0
 

Author Comment

by:Robson8112
ID: 8157875
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.
0
 

Author Comment

by:Robson8112
ID: 8157962
Webwoman,

The field title is Date, should key = date?

help!
0
 

Author Comment

by:Robson8112
ID: 8158111
I dont think the script is reading in the values of Date1 and Date2 on the form
0
 
LVL 19

Expert Comment

by:webwoman
ID: 8159707
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.
0
 

Author Comment

by:Robson8112
ID: 8165367
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

0
 
LVL 19

Expert Comment

by:webwoman
ID: 8170030
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.
0
 

Author Comment

by:Robson8112
ID: 8193939
in my access tables, the date is shown as dd/mm/yy, field is set to short date
0
 

Author Comment

by:Robson8112
ID: 8195226
if the date input on the form is a string, how do i change it to read dates ????
0
 
LVL 19

Expert Comment

by:webwoman
ID: 8200593
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.
0
 

Author Comment

by:Robson8112
ID: 8202008
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"-->



0
 
LVL 19

Expert Comment

by:webwoman
ID: 8204672
Change any field named DATE -- it's a reserved word.

0
 

Author Comment

by:Robson8112
ID: 8209131
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!
0
 
LVL 19

Expert Comment

by:webwoman
ID: 8214847
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.
0
 

Author Comment

by:Robson8112
ID: 8216782
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"-->
0
 

Author Comment

by:Robson8112
ID: 8216796
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 :-) )
0
 

Author Comment

by:Robson8112
ID: 8237303
no one can help?
0
 
LVL 19

Expert Comment

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

Author Comment

by:Robson8112
ID: 8243907
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... :-(
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8244807
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...


0
 

Author Comment

by:Robson8112
ID: 8245234
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)


0
 
LVL 15

Expert Comment

by:gladxml
ID: 8250046
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...
0
 

Author Comment

by:Robson8112
ID: 8252369
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>

0
 

Author Comment

by:Robson8112
ID: 8268010
can nobody help? surely someone has does this before?
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8268101
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
0
 

Author Comment

by:Robson8112
ID: 8290238
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
0
 

Author Comment

by:Robson8112
ID: 8290285
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
0
 

Author Comment

by:Robson8112
ID: 8290304
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
0
 
LVL 15

Accepted Solution

by:
gladxml earned 200 total points
ID: 8298366
Robson8112,

Below is the working code...
 
=====

<%
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("Dates")  & "<br>"
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="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>


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

===========

HTH...

HAppy programming....
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8298442
Robson8112,

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

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

HAppy programming...

Regards,
gladxml
0
 

Author Comment

by:Robson8112
ID: 8298835
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>
0
 
LVL 15

Expert Comment

by:gladxml
ID: 8298860
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....
0
 

Author Comment

by:Robson8112
ID: 8298954
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.
0
 

Author Comment

by:Robson8112
ID: 8325208
Glad,

Thanks for your time

The points are yours :-)



0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

770 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