TexasWebDevelopers--A Dallas Texas Web Design and Development Company.

• Portfolio • FAQ's • Pricing • Request Info • Code Examples • Demos • Marketing • Tutorials • Downloads •

An example of a database query by date

Here we have a database named test.mdb in a folder named database with field names First_Name, Last_Name, and Date_Expired in a table named clients. In the Access database the Date_Expired field has a data type of Date/Time and the Default Value is Date()+30 this means that every time a new entry is made in the database this field will automatically be a date that is "that day's date plus thirty days" and in the format mm/dd/yyyy). The results we are asking for here are "all the first and last names whose expiration date is between today and thirty days from now". The sql statement looks like this: "SELECT First_Name,Last_Name,Date_Expired FROM Clients Where Date_Expired Between Date() And Date()+30 ORDER BY Date_Expired ASC"

Here are the results:

Billy Bones 9/15/2010
Tim Tune 9/30/2010
Mike Amos 10/1/2010

Here is the code for the entire page:

<% Option Explicit %>
<% 
Dim conn,strConn 
Set conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=MICROSOFT.JET.OLEDB.4.0; " & "DATA SOURCE=" & Server.MapPath("../database/test.mdb")
conn.Open strConn
'Explicitly Create a recordset object 
Dim rs 
Set rs = Server.CreateObject("ADODB.Recordset") 
'Open our recordset 
Dim strSQL 
strSQL = "SELECT First_Name,Last_Name,Date_Expired FROM Clients Where 
Date_Expired Between Date() And Date()+30 ORDER BY Date_Expired ASC" 
rs.Open strSQL, Conn 
%> 
<HTML>
<Head>
</Head>
<title>Query by Date DB Results Example</title> 
<BODY bgcolor="#FFFFFF">
<% 
Do While Not rs.EOF 
Response.Write(rs("First_Name") & " " & rs("Last_Name") & " " & rs("Date_Expired") 
& "<BR>")
rs.MoveNext 
Loop 
%>
</BODY> 
</HTML>
<%
rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing
%>