Need code to remove entries 1

[avayaman]

I need the code to remove all entries from a databse with a date attached over 365 dys.

It needs to remove all entries based on 2 fields.

The fields are "status' & "Duespaid"

The table is tbldata

I can remove them based on one parameter, eg:

strSQL = "delete * from tblData where status is "archived'"
DoCmd.RunSQL strSQL

I know this is wrong, but it give the idea of what I need to do & I can't get the syntax right

strSQL = "delete * from tblAData where status='Remove'" _
& " WHERE Date > Duespaid.value + 365"

DoCmd.RunSQL strSQL
DoCmd.ShowAllRecords


DoCmd.RunSQL strSQL

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[jrbarnett]

strSQL = "delete * from tblAData where status='Remove'" _
  & " AND DateAdd ("d", 365, Duespaid.value) < date() "

John

 

[avayaman]

That was fast, but it does not work. I get an unexpected end of statement compile error, The "D" is highlighted.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[jrbarnett]

Oh, its the quote marks inside quotes, try:

strSQL = "delete * from tblAData where status='Remove'" _
& " AND DateAdd (""d"", 365, Duespaid.value) < date() "

John

 

[avayaman]

That did it, Thanks a lot !!!!

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[PHV]

A simpler way:
strSQL = "DELETE * FROM tblAData WHERE status='Remove'" _
& " AND Date()-Duespaid>365"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[avayaman]

Thanks

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com