Delete data by month

Posted on 2014-07-17
Last Modified: 2014-07-23

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

Question by:Seamus2626
    LVL 20

    Accepted Solution

    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
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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

    Author Comment

    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

    LVL 20

    Expert Comment

    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
    LVL 20

    Expert Comment

    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

    Author Comment

    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!


    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now