Solved

Access VBA SQL Delete Query

Posted on 2010-09-22
2
472 Views
Last Modified: 2012-05-10
Ok the below code ask for data in last 30 or 31 dates.

How would I tweak it to delete everything BUT the last 30 or 31 days?

Private Sub cmdProcess_Click()
'Date_DateFellDuringLast30Or31Days
Dim SQL

SQL = "DELETE  * FROM Table1 "
SQL = SQL & "WHERE Table1.SalesDate Between dateserial(year(Date()),month(date()),day(date())) And dateserial(year(Date()),month(date()) -1,day(date()))"
CurrentDb.Execute SQL, dbFailOnError

MsgBox "Complete", vbOKOnly, "Process Complete"

End Sub

Open in new window

0
Comment
Question by:ca1358
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33738590
Private Sub cmdProcess_Click()
'Date_DateFellDuringLast30Or31Days
Dim SQL

SQL = "DELETE  * FROM Table1 "
SQL = SQL & "WHERE NOT (Table1.SalesDate Between dateserial(year(Date()),month(date()),day(date())) And dateserial(year(Date()),month(date()) -1,day(date())))"
CurrentDb.Execute SQL, dbFailOnError

MsgBox "Complete", vbOKOnly, "Process Complete"

End Sub
0
 

Author Closing Comment

by:ca1358
ID: 33738611
Thank you!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question