Question regarding SQL Between and date range...

[aamaker]

I have a simple access 2002 database
field: ID (autonum)
field: entered_date (date dd/mm/yyyy)

And want to allow users to select a FROM date (text field using dd/mm/yyyy format) and a TO date (text field using dd/mm/yyyy format)...


The problem Im running into is in passing the variables for the from date and to date in the right format...

when I replace the variables with dates entered directly into the sql statement ( select * from tbl_name where entered_date BETWEEN #1/1/2000# AND #1/1/2003# ) it works fine...

but how can I replace those dates with the variable names and still use the #'s to get this to work... below is the code Im using...


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

<%

If request.form(&quot;submit&quot <> &quot;&quot; Then



Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file


' MapPath of virtual database file path to a physical path.
' If you want you could hard code a physical path here.
strDBPath = Server.MapPath(&quot;mydatabase.mdb&quot


' Create an ADO Connection to connect to the scratch database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set cnnSimple = Server.CreateObject(&quot;ADODB.Connection&quot

' This line is for the Access sample database:
cnnSimple.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & strDBPath & &quot;;&quot;


dim varfromdate, vartodate

varfromdate = request.form(&quot;dte_from&quot
vartodate = request.form(&quot;dte_to&quot


' Execute a query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
On error resume next
Set rstSimple = cnnSimple.Execute(&quot;SELECT * FROM tbl_data where entered_date between varfromdate AND vartodate&quot


if err.number <> &quot;&quot; Then
response.write err.number & &quot; -- Sorry, I found some issue and cant continue...&quot; & vbCrLf
response.write err.description
Response.End()
End If


' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
%>
<table border=&quot;1&quot;>
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields(&quot;id&quot.Value %></td>
<td><%= rstSimple.Fields(&quot;entered_date&quot.Value %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSimple.Close
Set rstSimple = Nothing
cnnSimple.Close
Set cnnSimple = Nothing


End If%>

__________________________________

 

[bhoge]

hi aamaker

if u can use the query without # then try this :
SQL=&quot;SELECT * FROM tbl_data where entered_date between '&quot; & varfromdate & &quot;' AND '&quot; & vartodate & &quot;'&quot;

write this query in one line.
knowledge is meaningless until it is unshared.

 

[Bullschmidt]

Or I'd suggest with the #:

Set rstSimple = cnnSimple.Execute(&quot;SELECT * FROM tbl_data where entered_date between #&quot; & varfromdate & &quot;# AND #&quot; & vartodate & &quot;#&quot

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer

http://www.Bullschmidt.com

ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

 

[aamaker]

Awesome.

That worked perfectly Bullschmidt... still working on learning how/when to use quotes in my query strings.

Thanks!

 

[Bullschmidt]

Great to hear it and here's a little tip about SQL syntax:

Remember to put # before and after a date field variable, ' before and after a text field variable, and nothing before and after a number field variable.


Best regards,
J. Paul Schmidt, Freelance ASP Web Developer

http://www.Bullschmidt.com

ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...