Auto Delete Database After X Days
Sep 23, 2014There is a way in order to auto delete a database after e.g. 7 days ...
View RepliesThere is a way in order to auto delete a database after e.g. 7 days ...
View RepliesI have built a db where users enter the requested information, can update, search etc. However, after about 90 days the infomration is really not any good anymore. I would like to be able ot have a way to auto delete info that it over the 90 days.
I have a column named (date) and it automatically creates the date and time via Now() function. It would be nice to have something that would delete via that columns date.
Any suggestions?
I know that I can query and delete multiple records via Access but I have worked hard to keep me out of the day to day db funtion.
I'm using access database. i want to code my database so that my database automatically send email notification to client and also to admin of loan expire information ( Expired date and Loan Limit which is already in my database).
View 14 Replies View RelatedI am creating a web database where users submit entries using an online form. When they submit the entry, I am saving the entry date and time in a column called Timestamp. What I need to figure out is if there is a way to create some sort of expiration date, based on the Timestamp, that will automatically delete a record after it is 7 days old. Is there a way to do this? Thanks so much for any help that you can give.
View 5 Replies View RelatedHey.
I have created a button which when clicked imports a table from Excel into Access. Every month the users want to click on this, they want to update the Access table with the updated info in the excel table. My problem is that the whole table is imported again and not just the new rows which have been added in excel. I was just wondering if there was a way to have a button which when clicked it clears all of the records out of my table in access, instead of manually deleting them. So that my users can just click this and then click the import button.
Thanks in advance for your help.
I have a database of personnel. People come and go all the time; however I can't have a personnel record deleted as soon as they leave. They remain on our books until a specified date for each individual. The date is different for each individual. I was hoping to have a field in their record with the date, and have a query or some code delete that record on the date in the field. Is this possible?
View 7 Replies View RelatedHow do I write a Access 2010 Web database expression to give me the number of days between a particular field eg [sold date] and todays date?
Being a Web database I know I am restricted to a smaller list of available expressions - normally part of my expression would include eg date().
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
Hello everyone,
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer
dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Else
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday
strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"
'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates
If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If
Else
Sunday.Value = 0
End If
DayofWeek.Value = Format(dteToday, "dddd")
End Sub
Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
your help would be much appreciated
merry christmas to you all
Jabez
Is it possible to delete an entire seperate database from within a database via code after an uodate procedure has been carried out on a click event of a commad button, what would the code be to acheive this if its possible??:rolleyes:
View 3 Replies View RelatedHello
I am planning a key database
I have planed to use access and visual basic.
VB to add and delete keys.
Access to do the rest.
Is this a good solution? Do you have a better one, pleas let me know.
I need help setting up the delete and add buttons, I am bad i vb.
And I need help with the Delivered button, when I push the button I want it to delete all the information on the page. But print a backup or save it as a file, do you have a good idea.
When I press the “add key button”, I want I to add a key.
If you have 3 keys, and press ADD key, then it adds a 4 key and it must be a scroll window. Then I can choose the key I gave him
There is a image of the plan I am having.
I have tried to rename or delete in MS Explorer, but this just creates confusion. The databases still show up in bits and pieses on various screens. I've purchased 2 additional how to do it books on MS Access 2007 and find nothing on deleting or renaming an entire database and all objects or references to it. Does anyone know the magic bullet?:confused:
View 3 Replies View RelatedDear All,
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
'xxxxxxxxxxxxxxxxxxxxxxxx
Can someone please direct me to the right path.
Your help is highly appreciated.
qwerty70
I have recently gotten a handle on ASP and found it to be a very powerful tool.
I am wondering if there is a way to design a page where I can list my database tables, and by using a command, I can use asp to do the following.
1. Add or delete table
2. Add, delete, or update fields within the tables
for instance: rename a field 'PersonName' to 'MyName' without change the original values in the field 'PersonName'
I have searched all over the net and could not find information on this subject.
Thanks in advance.
I just upgraded to Access 2010. I can't delete or paste database objects in the table, form, query lists, etc.. Is there a setting that allows this?
View 6 Replies View RelatedI have a database with a main database that has 2 related tables...my problem is that i only keep data that i really need, in the main table. My problem is that i sometimes need to go back to old data that i erased. so i have to keep old data too. but i can;t on the same database. so i need to move the old records from the main table and all the related data to another database that will keep all the erased data, so if i need to find something i will search on the "deleted records" database.
View 6 Replies View RelatedI have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.
The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.
I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.
Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?
I wrote the following statements to update a SQL server database:
Dim MyDb As Database
Dim MyRs As Recordset
Set MyDb = DBEngine.Workspaces(0).OpenDatabase("AMD", dbDriverPrompt, False, "ODBC;DATABASE=AMD;DSN=Remote")
Set MyRs = MyDb.OpenRecordset("SELECT AllAttendanceEvents.* FROM AllAttendanceEvents ORDER BY AllAttendanceEvents.EntryTime DESC")
MyRs.MoveFirst
While Not MyRs.EOF
MyVal = MyRs!EntryTime
Debug.Print MyVal
MyRs.Edit
MyRs!Ix = 50099
MyRs.Update
MyRs.MoveNext
Wend
When it execute the red line statement, it generates the error:Cannot update. Database or object is read-only.
This error is generated isnpite that I already checked the boxes: SELECT, INSERT, UPDATE, DELETE in the Permissions page of the tables?
Please advise.
I have a table called Jobslog which contains records for my permanent jobs I have to do for customers in one week.Structure is like:
JobID
TeamID
DayName
JobDescription
Instructions
Each job is assigned to a team, but is following same schedule every week.Can I create like a module or query to autopopulate the table with next week jobs, but putting the date as well, not only day name.For example, each Monday job should have a date field which should be 8/12/2014, Tuesday jobs should have 9/12/2014 and so on until Sunday.Usualy they are more jobs for each day (not day and job).