Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ASP - Displaying Access records between certain date ranges

Posted on 2003-03-12
55
Medium Priority
?
201 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

581 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