Delete Records Using A Date Field

I've got a table with three fields:

ref (Autonumber)
notice (memo)
expdt (Date)

I'm trying to use an SQL statement to delete all records where expdt < the current date (Now). Here is the code I'm using:

<%
If Request.Querystring("act") = "clean" Then

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnStr

strExpdt = cdate(Now())

sql = "DELETE FROM tblNboard WHERE expdt < " & Now()

conn.Execute sql

conn.close
set conn = nothing

strResponse = "All expired notices have successfully been deleted."

End If
%>

However, this just gives me the following error:

Syntax error (missing operator) in query expression 'expdt < 11/12/2003 19:54:25'.

What am I doing wrong??

If you are using Access, I believe you must surround your inputted dates with pound signs. For SQL Server, they must be in single quotes. So I think you'd do something like this:

sql = "DELETE FROM tblNboard WHERE expdt < #" & Now() & "#"

Hope that helps.

Jill

OK, I tried the following SQL statement:

sql = "DELETE * FROM tblNboard WHERE expdt < # " & Now() & " #"

and it deleted all the records in the table up the 12/11/2003...for some reason it doesn't delete the other records in the table up to todays date (11/12/2003)!!

Any ideas what is going on here??

Oh, your dates are non-US dates then.
I was reading the date wrong, I guess.

Today's date US format is 12/11/2003
Where you are is 11/12/2003.

Perhaps you need to format the date differently in your expression...

Take a look here:

http://www.iisfaq.com/default.aspx?View=A284&P=119

Sorry, I should have mentioned in my post that all dates are GMT (including the server date) and the locale I'm using is UK. So I don't see there being a problem with the date formats if everything is using GMT.

It's still something with the dates...

Access should accept whatever the server locale/date setting is. Obviously that is working if your NOW() function returns the correct date.

Maybe try an input mask in Access? I'm not sure anymore. Sorry I'm no help.

J

Hey,

The reason your SQL isnt deleting the records for the current date is because you are asking your SQL to delete the records < [ Less than ] current date. Try using <= with the SQL, it should help....

Regards