Delete data by month

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

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

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

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