• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Delete data by month

Hi,

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

Apr
May

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

Thanks
0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
2 Solutions
 
clarkscottCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to get the month name:
http://www.techonthenet.com/access/functions/date/monthname.php

you should also return the year (and month number) info, as it will become important later
http://www.techonthenet.com/access/functions/date/year.php
http://www.techonthenet.com/access/functions/date/month.php

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;
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
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
0
 
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

Thanks
ScreenShotEE.docx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
clarkscottCommented:
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
0
 
clarkscottCommented:
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
0
 
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!

Thanks
EE-Pilot-DB.accdb
0
 
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!!

Thanks



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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now