|
| |
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
 |
MS Access + ASP + Date range query - 12/5/2005 3:08:27
How do i resolve the following SQL query in order to capture the date range entered by the user?
The query as follows:
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where date_of_offer between #" & frDate & "# and #" & toDate & "# "_
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer ASC"
The field date_of_offer is set as date/time
Following error obtain:
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'date_of_offer between ## and ##'.
Help required urgently. Thanks.
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 9:19:51
quote:
& frDate & "# and #" & toDate
Hi and Welcome to OutFront
Unless you have filled those variables with date field info beforehand, you probably should be using something like:
... & Request.form("frDate") & "# and #" & Request.form("toDate ") ...
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 9:42:44
Do I add in the statement in between the SQL statement as I'm getting the following error:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/RI_Project/Weekly_Report.asp, line 90, column 33
& "where date_of_offer between " #"& Request.Form("frDate") &"# and #" & Request.Form("todate") &"# " _
for the following action:
where date_of_offer between " #"& Request.Form("frDate") &"# and #" & Request.Form("todate") &"# " _
Please help. TQ.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 9:46:57
quote:
...date_of_offer between " #"& ...
Looks like you have an extra quote in there - see it? Try it this way:
..date_of_offer between #"& ...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 10:00:22
I'm getting the same error message as previously:
Error Type:
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'date_of_offer between ## and ##'.
The entire page coding(maybe it'll show something):
<% 'Create object. In this case Connection to a database
Set Conn = Server.CreateObject("ADODB.Connection")
'Select provider
'Open the connection
conn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &Server.MapPath("Risk_Entry.mdb")&""
'Create recordset
Set Rs = Server.CreateObject("ADODB.Recordset")
%>
<%
Dim frdate, todate
<!-- C01 Retreive the input of this page -->
frDate = request.QueryString("frDate")
toDate = request.QueryString("toDate")
%>
<form action="Weekly_Report.asp" method="get">
<table>
<tr>
<td><b>Date of Offer:</b></td></tr>
<tr>
<td>From Date:
<td><input name="frDate" size="8" value="<%= frDate %>" /></td>
<td>(mm/dd/yyyy)
</tr>
<tr>
<td height="25">To Date:
<td height="25"><input name="toDate" size="8" value="<%= toDate %>" /></td>
<td height="25">(mm/dd/yyyy)
</tr>
<tr>
<td height="25">Business Type:
<td height="25"><select name="buss_type" class = "textbox">
<option value="buss_type">New</option>
<option value="buss_type">Renewal</option></td>
</select></td>
<p> </p>
<p> </p>
</tr>
</table>
<td>
<FORM method="GET" action="weekly_report.asp">
<input type="submit" value="Submit">
</form>
</td>
<%
If frDate = "" AND todate = "" then
strsql = "SELECT Acceptance_No, Date_of_Offer,Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "from risk_acceptance "_
& "where date_of_offer between #"& Request.Form("frDate") &"# and #" & Request.Form("todate") &"# " _
& "GROUP BY Acceptance_No, Date_of_Offer, Buss_Type, Ceding_Co, Insured, inception_date, "_
& "Expiry_date, mpib_share " _
& "ORDER BY Date_of_Offer ASC"
end if
Rs.Open strSQL,Conn, 1,3
%>
<TABLE>
<table BORDER="1" align="center">
<br>
<br>
<tr>
<th bgCOLOR=#CCCCCC>Acceptance No</th>
<th bgCOLOR=#CCCCCC>Date of Offer</th>
<th bgCOLOR=#CCCCCC>New Biz/Renewal</th>
<th bgCOLOR=#CCCCCC>Cedant</th>
<th bgCOLOR=#CCCCCC>Insured Name</th>
<th bgCOLOR=#CCCCCC>Inception Date</th>
<th bgCOLOR=#CCCCCC>Expiry Date</th>
<th bgCOLOR=#CCCCCC>MPIB Share</th>
<th bgCOLOR=#CCCCCC>Fire</th>
<th bgCOLOR=#CCCCCC>Engr</th>
<th bgCOLOR=#CCCCCC>Motor</th>
<th bgCOLOR=#CCCCCC>MISC</th>
<th bgCOLOR=#CCCCCC>Bond</th>
<th bgCOLOR=#CCCCCC>PAI</th>
<th bgCOLOR=#CCCCCC>Libiality</th>
<th bgCOLOR=#CCCCCC>Oil&Gas</th>
<th bgCOLOR=#CCCCCC>Aviation</th>
<th bgCOLOR=#CCCCCC>Marine Hull</th>
<th bgCOLOR=#CCCCCC>Marine Cargo</th>
<th bgCOLOR=#CCCCCC>Medical</th>
<th bgCOLOR=#CCCCCC>Total Gross Premium</th>
</tr>
<%
' Move to the first record
rs.movefirst
' Start a loop that will end with the last record
do while not rs.eof
%>
<tr>
<td><%= rs("acceptance_no") %></td>
<td><%= rs("date_of_offer") %></td>
<td><%= rs("buss_type") %></td>
<td><%= rs("ceding_co") %></td>
<td><%= rs("insured") %></td>
<td><%= rs("inception_date") %></td>
<td><%= rs("expiry_date") %></td>
<td><%= rs("mpib_share") %></td>
<%
' Move to the next record
rs.movenext
' Loop back to the do statement
Loop
conn.close
Set conn = Nothing
%>
</table>
</body>
</html>
Appreciate your help here.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 10:11:40
quote:
I'm getting the same error message as previously:
I apologize if I insult you but are you posting to this page from a form? If you're just browsing to this page, it won't work.
If you are posting to this page, what are your field names? The names in the request.form items must correspond with your form field names.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
|
RE: MS Access + ASP + Date range query - 12/5/2005 23:32:10
quote:
& Request.form("frDate") & "# and #" & Request.form("toDate ") ...
I'm getting the results within the same page. Hence by right the input request should be as so (have changed accordingly):
frDate = Request.Form("frDate")
toDate = Request.Form("toDate")
the form fields names are "frDate" and "toDate".
Still unable to pass the query stage. Same error msg. Plz help.
| |
|
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
|
RE: MS Access + ASP + Date range query - 12/6/2005 9:01:56
Nope. It doesn't resolve the error.
Any other possible causes?
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS Access + ASP + Date range query - 12/6/2005 10:06:25
Looks like you may have an extra form tag in here:
...
<td>
<FORM method="GET" action="weekly_report.asp">
<input type="submit" value="Submit">
</form>
</td>
...
Remove the form tag in there so it looks like this:
...
<td>
<input type="submit" value="Submit">
</form>
</td>
...
and change the first form line from this:
<form action="Weekly_Report.asp" method="get">
to this:
<form action="Weekly_Report.asp" method="POST">
That help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
oracle
Posts: 53
Joined: 12/5/2005
Status: offline
|
RE: MS Access + ASP + Date range query - 12/6/2005 20:46:42
I have done the changes but still get same error on the date error syntax.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: MS Access + ASP + Date range query - 12/6/2005 20:50:26
Do you have a URL?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
