Sql Between Using Dates

Guys, I really need your help.

I am running a query from an ASP page as shown below

"SELECT * FROM Appointments WHERE GPID = '" & this_GP & "' AND Date BETWEEN '" & start_date & "' AND '" & End_date & "' "

Now it does retrieve the information held between the dates on the database (ACCESS) although my problem is this

If I am querying between 31/3/04 and 6/4/04 it retrieves all the records up to the 31rst of the 4th ! ! The data is held on the database in dd/mm/yy format and I have set the Server.LCID on my page to dd/mm/yy. I have even tried changing the ' for # in the sql statement.

Please help - its part of my final year project for uni and I am

Thanks
Mark

In queried Access can onlly handle dates in mm/dd/yy format. otherwise it gets confused. There is some knowledgebase that I read a week or two about it, but I can't remember which one.

If I remember correctly you don't want single quotes around your variable either (makes it think it is a string)

S_

Thanks Sbaxter for the help but I eventually had to give in to microsoft since Access Queries its tables using mm/dd/yy format.

All sorted now though !
Thanks Again
MT.

MTFLYNN,

Have you solve your problem??

My problem is almost same as yours.

tempLastDate = DateValue(Session("LastLoginDate"))
SQL = "SELECT count(*) as DateMatch FROM tblLogin WHERE DateLogin > '"&tempLastDate&"'"
Set rsNotice = connNotice.Execute(SQL) //line 19

//the value of session("lastLoginDate") is 04/04/2004, dd/mm/yyyy, data type is date/time in microsoft access.
//connNotice is just a object connection
//tblLogin is a table, DateLogin contains date value which in dd/mm/yyyy format.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/project/Code/Home.asp, line 19

Do you have any clue to solve this? if is due with the format, can you tell me how to convert the format in asp?

regards,
aiyong

try this