 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / March 2008delete records
Hi, My form and report that based on the table has many records. I would like the old records deleted by themselves if their check out dates are pass the current month. Ex: check out date: 02/08/08. It will be deleting by itself if today is 03/01/08 The goal is instead of manually delete the old records that pass the current month, I would like the database delete them for me. That will make the list shorter and easier to keep track. Thanks Chi This might not be what you want, but I'm definitely against deleting old records. Once they're deleted you have no Archiving possibilities in the future. Easiest thing to do is just create a query with the data in it. And then under the criteria in the date column put whatever the criteria is you want. Like Date() - 3 or >#2/08/08# Then your query will be constantly up to date and you can still access old records in the tables. > Hi, > [quoted text clipped - 11 lines] > Thanks > Chi Or at the very least, if your table grows very large very quickly, append the poor records to an Archive table before deleting them from your present table. When you promise faithfully to do that, then we'll all start talking about Append and Delete Queries. The number of times I've deleted saying 'Well, I'll never need to refer back to those again!"..... Evi > This might not be what you want, but I'm definitely against deleting old > records. Once they're deleted you have no Archiving possibilities in the [quoted text clipped - 22 lines] > > Thanks > > Chi Thank you AKphidelt, I really don't need the old records. However, thank you for your respond and the useful criteria. Chi > This might not be what you want, but I'm definitely against deleting old > records. Once they're deleted you have no Archiving possibilities in the [quoted text clipped - 22 lines] > > Thanks > > Chi If you always want only records that are greater than the beginning of the current month you can use the following criteria in a query to see only those records Field: SomeDateField Criteria: >= DateSerial(Year(Date()), Month(Date()),1)  SignatureJohn Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County . > Thank you AKphidelt, > [quoted text clipped - 36 lines] >> > Thanks >> > Chi Thanks for the criteria. However, I would like to delete the old records out off the database. I am sorry if I am asking different question. After the user entered the CHECK OUT DATE and close the form, that record will be deleted. Ex: In record 10, I enter 03/24/08 in the CHECK OUT FIELD and then close it. I would like the record 10 will be deleted by itself so that when I reopen the form, the record 10 will be gone. Thanks Chi > If you always want only records that are greater than the beginning of the > current month you can use the following criteria in a query to see only [quoted text clipped - 43 lines] > >> > Thanks > >> > Chi In the OnOpen Event of the form, just above the End Sub line, add this code Dim MySql As String My Sql = "DELETE YourTable.* FROM YourTable WHERE (((YourTable.YourDateFeild)=Month([YourDateField])<Month(Date())))" DoCmd.RunSql MySql Replace YourTable and YourDateField with the real table and field names. You'll be sorry! Evi > Thanks for the criteria. However, I would like to delete the old records out > off the database. [quoted text clipped - 65 lines] > > >> > Thanks > > >> > Chi If you've got any months lower than the current date from next year then they will be deleted too so Lets make that: Dim MySql As String My Sql = "DELETE YourTable.* FROM YourTable WHERE (((YourTable.YourDateField)<DateSerial(Year(Date()),Month(Date())-1,Day(Date ())))); (darned rosé) Evi > In the OnOpen Event of the form, just above the End Sub line, add this code > [quoted text clipped - 90 lines] > > > >> > Thanks > > > >> > Chi Thanks Evi, I entered the code, but I have "syntax error" Here is the code: Private Sub Form_Open(Cancel As Integer) Dim MySql As String MySql = "DELETE EQUIPMENT TABLE 2.* FROM EQUIPMENT TABLE 2 WHERE" (((EQUIPMENT TABLE 2. CHECKEDOUT)=Month([CHECKEDOUT])<Month(Date())))" DoCmd.RunSQL MySql please help Chi > In the OnOpen Event of the form, just above the End Sub line, add this code > [quoted text clipped - 90 lines] > > > >> > Thanks > > > >> > Chi If you've got gaps in your table names then you'll need to put square backets around it [Equipment Table 2].* See my next letter for a better code though. Don't want you deleting future data. And please make a copy of your db and check the query on there, before deleting anything using a delete query. Once you say OK to it, everything will be gone, forever!!! Evi > Thanks Evi, > [quoted text clipped - 105 lines] > > > > >> > Thanks > > > > >> > Chi |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.