Delete data by month


I need a form which deletes records in a table based on a column called Date

In my table, i have months April-May populated


Can anyone provide info on how to create a form, where the user gets all the months in the table as a list


Then they can click on the month, and another button on the form will say delete, if they click delete, a query runs which removes the data in the table based on the month clicked e.g. May

All help would be appreciated

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a query called qryByMonth.

Add the Primary key to the results.  Add new column called "month"
Month: format([monthfield],"mm")

Now, using this new query, create a Delete query with the criteria Where [Month] = "04".

There are other methods, but this is an instruction on how to add a calculated field to a record source, then use it from a different object (in this case - a delete query).  

PS.  This method also is great for report record sources (where calculated fields are "sum'd" in the footer sections.)  Instead of complex calcs... simply add this calculated field to the report just like any other field.

Scott C

Scott C

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to get the month name:

you should also return the year (and month number) info, as it will become important later

doing a distinct or group by on the original data should do the job to list the possible year-month values.

first note: this will not be too good in performance if you are having lots of records in the table (lots meaning thousands or more / month).  if this is the case, you shall think about not using ms access as the "database" part, and eventually use another RDBMS, but may keep ms access as front-end.
second though is to keep the list of months with data in a separate table, kept up to date with either some "triggers" or regular extractions from your actual table...

next, to delete the data, you could use the same functions as above to know which records to delete.
again, if the number of records is manageable, using above functions will work:
DELETE yourtable WHERE MONTHNAME(yourfield) = 'April' AND YEAR(yourfield) = 2014

otherwise, you shall need to think about handling the date fields correctly, in general discussed here;
DELETE yourtable WHERE yourfield >= #2014-04-01# and yourfield < #2014-05-01#
it would need a bit of coding to get the correct starting and end values for the range delete, but it will be better in the long run
Seamus2626Author Commented:
Thanks Scott,

Ive created a list box (as you can see in the attachment)

I would like some code that when the user selects the month, they get a message box

"Are you sure you want to delete data"

If yes, it uses that month and goes to "All_Data_Static", it then deletes out all rows where the field "Date" equals the month they have selected

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

further... and much easier.....

Create the new query as I mentioned in previous.
Add the new column called DateToDelete: format([yourdatefield],"yyyymm")

Now - create a new delete query;

Delete (your stuff)
Where [DatetoDelete = '201404'

Don't need a lot of code - no fancy and busy functions.  Just provide a place to enter the search criteria (201403, 201404, etc.)

Scott C
Scott C
In your case example:
Create a query with a calculated column called MothToDelete: format([yourdate],"mmm")
This field will display the first 3 letters of each month.

(Another powerful and easy thing)
Create a public variable

Public glbMonthToDelete as string

Create a function to retrieve this value:
function get_glbMonthToDelete() as string
get_glbMonthToDelete = glbMonthToDelete
end function

Now, all you have to do is assign a month to the variable glbMonthToDelete and you can use this new function to get the value (FROM ANYWHERE IN YOUR APPLICATION!  It could be the source of a text box, criteria in a query, default value anywhere).  See, you can't retrieve the value of a variable in many places - put you can run a function everywhere.  So.......

Create a DELETE query (using this new query) and simply set the criteria to  column MonthToDelete = get_glbMonthToDelete().  Save this query.

Now, from your form,  the user will select a date/month from the dropdown box.  On a button - simply add this code:

If not isnull(me.yourdropdownbox) then
       glbMonthToDelete = me.yourdropdownbox
       docmd.OpenQuery "The Name of your new Delete Query"
end if

That's it.    Now remember, you can use the Public variable/public function "get" for tons of things.  This allows you to build re-usable queries - hard coded into your system (so the execution plan doesn't have to be rebuilt each time you run it).   You also can run these types of queries from any form, any procedure, anywhere.   Just assign a value to your public variable and you can use it anywhere.

Scott C
Seamus2626Author Commented:
If im being honest Scott, a few of those steps seem to be beyond me, i have uploaded the DB, if you could get me started it would be much appreciated!

Seamus2626Author Commented:
Sorry for the delay guys, im trying to set this up, so i have my list box "Jan-Dec", i have my function in place, and a new button called delete. So im thinking a user selects the month from the listbox and then presses the button delete.

My problem here is my delete query is doing nothing!

When i run the delete query manually, i get the inbuilt box "Enter Parameter Value" get_glbMonthToDelete

I think im close but am missing a cruical step/link!!


My delete query
Blady blah
FROM All_Data_Static
WHERE (((All_Data_Static.Date)=get_glbMonthToDelete));

My function

Function get_glbMonthToDelete() As String
get_glbMonthToDelete = glbMonthToDelete
End Function

a module - Option Compare Database

Public glbMonthToDelete As String

My button

Public Sub Command12_Click()
If Not IsNull(Me.List10) Then
       glbMonthToDelete = Me.List10
       DoCmd.OpenQuery "Delete_Month"
End If

End Sub
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.