sql statement needed

[Incognito44]

Been playing with this one and havent had any luck..
How would you write an sql statement to delete any dates previous to today's date.
I've tried the following

ie
sql="delete from MyTable where TheDate<" & date
sql="delete from MyTable where TheDate<" & "#" & date & "#"
sql="delete from MyTable where TheDate<" & "'" & date & "'"

[techgnome]

SQL Server or Access?

[andreys]

If access:

sql="DELETE FROM [MyTable] WHERE [TheDate] < #" & date & "#"

[Incognito44]

I tried that one as well...
This is an Access db.
sql="delete * from [mytable]" deletes all records as expected but I only want to remove dates that have already passed.
sql=delete * from [mytable] where [thedate]<" & date
sql=delete * from [mytable] where [thedate]<" & "#" & date & "#"
sql=delete * from [mytable] where [thedate]<#" & date &"#"

have all been tried unsuccessfully.....
anyone? thx

[jayakumar]

hi,

try this

db.execute "delete * from [myTable] where [Datefield]<#" & date & "#"

[shahid]

hi try this


con.execute "delete from table where dt < #" & now() & "#"

before it confirm that ur datafiled name is not date

if ur datafiled name is date then u can't delete records..
i hope u confirm it

[Incognito44]

The Date field is called TheDate just for that reason.
I've tried all the suggestion and still can not get dates < todays date to delete from the table.

[andreys]

Originally posted by techgnome
SQL Server or Access?

What is a variable name for your date parameter?
Could you post your code here?

[Static]

are you running the SQL form inside Access???

I just ran this :

DoCmd.RunSQL ("delete * from test where thedate < #" & Date & "#")


(test was my table)

and it worked fine..deleted everything before today