Posted: February/06/2005 at 1:09pm | IP Logged

I am trying to pull in some dates from a access database table, but for some reason I am getting unexpected results. 

Instead of the dates between the range I am getting every single date.  Any ideas as to what I am doing wrong?  Im guessing it is something very simple.

Here is the code I am using:

Dim cnn,rst
Dim date1, date2

date1 = CDate(date())
date2 = CDate(date()+30)

set cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strEzDBPath2) ' "../db/ez.mdb"
sqltext = "SELECT * FROM tblCalendar WHERE " & CDATE(date) & " BETWEEN " & date1 & " AND " & date2 &""
set rst = cnn.Execute(sqltext)
Response.Write rst("Date")

if not rst.EOF then 'just added
while not rst.EOF

Response.Write rst("Date")

rst.movenext
wend
end if
rst.close
cnn.close

Posted: May/27/2006 at 10:02am | IP Logged

first you have to change the field name in the database from Date to sometihng else

because the word date is reserved for the system so when u use it the server will conflict thinking that date is a function to show the date and doesnt know that you mean the field name

second the syntax for the between is

SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2

so after the word WHERE must be a column_Name not the date or the field date

try thant and should work

Best Regards