Hi,
Firstly I rather unsucessfly tried the search function,
I want to automatically delete any enteries older than 6 months, I do NOT want to archieve them, simply delete them. I only have three entries;
Date
Address Line 1
Post Code
I was unsure how i would go about this or even where to begin so some help would be much appreciated.
Kind regards
Is there an expression to show records in a query that are older than 6 months?
At the moment I have to go into the database every month and change the criteria date ie:
<#01/09/2007#
It would be much easier if it could do this automatically with an expression.
Can anyone tell me if ones exists?
Thanks
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
Querying a field for a number of days from today's date but not had a lot of luck finding how to query this from a date in the future.
Currently I have as criteria for the relevant field:
Code:
<[Period Start (dd/mm/yy - less 5 years from financial year end)]
which prompts for a parameter but using this I have to figure out the date 5 years from the future date first and then enter this.
Surely there must be code that allows me to simply enter the date, in the prompt and this then shows me data of 5 years or older equipment!?
1) I am trying to use the same set of data for different months. I will try to explain...For each month I want to mark the data as "Statement Received" and "Statement Reconciled". I don't want to create a brand new database every month with the same data. The database will become massive too quickly. Is there any way that I can set the date by using the filter date combo box I have created on the frm_SearchMulti form and having a fresh set of data for just April. When May comes around I can create a new date and have a fresh set of data, but still reference April data... I hope I am explaining this okay
2) Would it be possible to add a column into the "SearchResults" ListBox for Statement Received and Statement reconciled? I would want to update it through the text boxes I made directly above it. The buttons right now do not do anything... they are just there for formatting purposes. I want want to be able to select multiple record in the listbox and update them all simultaneously
3) How can I make the "New Account" button actually pull up a new account?
Hey Guys,
I've been racking my brains on this one and need your help. I was asked this question by a friend who has just set up a database for a non-profit organisation in the UK. I have attached an simple example database to illustrate the problem.
There is a table called "tblMembers" which contains data on members of clubs and the dates their membership expires.
There is a query called "qryExpiryCrossCheck" and it needs to display people whose membership has expired in the previous month (i.e. May 2005).
Sounds easy? Well the query needs to know that 'now' is June 2005 and that the previous month is May 2005 and then as I said display people whose membership has expired in the previous month.
Any ideas/solutions would be greatly appreciated. As I said this is a favour for a friend who is carrying out some free work for a non-profit organisation.
Cheers,
Rusty
:D
Im looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
Forgot to post it:
>=Now()-Day(180)
I have a query that pulls data for the current month and the last two months. It had been working fine until 2015. The problem is that it's looking for the last two months but for 2015. How do I edit my formula to pick up the two months and for it to know it's a different year??
Code:
CMSPM2: IIf([LOB]="CMS" And Month([Month])=Month([Forms]![Main Menu]![EndDate])-2,Count([Question]),0)
I have a small clinic database. I've got tblAppointments to show AppointmentID and AppointmentDate among various other data, but only the dates matter for what I'm trying to achieve.
I'm trying to show a chart on a report that shows the number of appointments by months for this year and previous year. How can I do this...without using SQL, hopefully?
Here's what I tried: I made a cross-tab query to successfully show the years 2013 and 2014 in the rows, months in the columns, and number of appointments as values. But then, I didn't know how to graph it to compare the number of appointments for the two years by months.
I realize that some data entries are no longer needed. I want to delete them. However, I have this data within a relationship dependent upon autonumbers. For instance, table 1 is linked to table 2 is linked to table 3. As of now, only data in table 1 exists. In the future, corresponding data in 2 and 3 will need to be entered. How can I make sure that by deleting some entries within table 1, that i will not jeopardize the functioning of tables 2 and 3? What does one do in a situation like this?
View 10 Replies View RelatedI have a project that was given to me because I have previous experience with MS Office, but my level of Access experience is somewhat below par. Here is my problem: We are using one form (Enter Required Training) to create a row of data that goes to a report to let us know when employees need to be trained by.
This report is called 'Past Due - Required Trng'. When employees have completed a training course we go into a form called 'Enter Training Attendance' to update another report to show that they have completed training. However, once employees have completed a course, we have to manually go to the database where the required training courses are stored to manually delete them so that they do not show up on the report.
My boss would like me to find a way to automatically delete the required training information without having to manually delete them each time. Is it possible to have the 'Enter Training Attendance' form delete the record created by the 'Enter Required Training' form so that it does not show-up on the report once completed? I have been messing around with the datasheets, events and even formatting but I can't seem to get anything to work.
Hi all,
Im trying to write a query so that all data in all but two tables is deleted, not the tables themselves only the data.
I keep getting these errors when im trying to build a suitable query..
COULD NOT DELETE FROM SPECIFIED TABLES
or
TOO MANY FIELDS DEFINED
With any luck i'd like to able to have this query run automatically each day is there any way to do that or do i have to repost in vba section?
Thanks for reading!!
My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).
At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.
I've just joined a company based in Scotland, they are running a multiple user access 2000 database: backend and front-end. Recently they have had an issue where a user inserting a new row in a table, as they do, the record above drops off the table (disappears/unrecoverable). My first impression has been that this is a DB corruption issue and as such have ran compact/repair.We are running this database on network server:
-Backend resides in central location
-Frontend - Each user has their own front-end, storded on their personal network drive.
Would I be right in saying that the best solution would be to convert to newer version of access ?
I'm using Access 2010. I have a form that has a combo box. The combo box lists all employee names and one has to be selected. I've been adding employees over time as new employees come. This DB is 3 years old so there are many employees showing in the combo box selection that are no longer here. I'd like to only have the current employees visible but I do not want to just delete them from the Table because then the historic data wouldn't be complete. How can I only show the current employees in the combo box and maintain the data in the table?
View 1 Replies View Relatedi need to make a query that returns all the records older than 24 hrs anyone help me with this one ?
View 11 Replies View RelatedI have a main form with several linked by ID worksheet sub-forms. I would like to add a on-click function to delete the main form and all related record on each sub-form. I have tried several methods but nothing is working correctly. The cascading delete function is not available as part of the form relationships. How I can accomplish this?
View 1 Replies View RelatedHi, we have a database for keeping record of our games (unreal tournament games).... Its just a simple database and we enter our results based on the following fields: -
Opponent:
Game Type:
Players:
Maps:
Date:
Result:
the file has started to get quite big and wondered if it would be possible to automatically move entries say that were older than a month old to a new table, or archive table. Im not a big access genious so i hope i have explained enough for you to understand what im trying to do here.
Thank you for your time, much appreciated.
Im not sure this belongs under FOrm but most likely.The thing is I have 5 tables and 5 forms each table connected to each form.My problem is when I enter data into the form and then it saves into the table.The next time I open my program and I enter another information into the form it goes into the table but it goes over the older one I entered..SO my problem is I can only save one record into the table because it overwrites the older one. Thanx
View 1 Replies View RelatedHi Guys, How do I make an SQL statement that returns records that are over a year old. I tried this...
SELECT * FROM tblExample WHERE Date<365
But that does not work.
Hi,
I'm new to using access SQL , I'll appreciate if someone can provide the where clause of the statement below:
Provide the count of assets with values older than current and previous versions.
This is needed to determine if we can upload these assets manually.
thanks
b2bmike
I need to find the LATEST date in field Recdate that is 45 days or older in a query. I have tried <Date()-45, etc. but it returns all dates not just the last one. Does this require a range of dates to do this ? If not, how would I id the last date input ?
Thanks
My database contain these:
- Products Table = [ID].AutoNumber, [Product].Text, [Active].Yes/No
- Orders Table = [ID].AutoNumber, [Product].Number, [Date].Date/Time, [Payment].Text
- Orders Form = [ID], [Product], [Date], [Payment]
I have [Product] in Orders Table and Orders Form as Combo Box for showing Product from Products Table.
And within product field on Orders, I want to make 'Active' product only showing up when choosing from combo box.
I made some records to test in Orders Form. Then I filtered Product from Orders Form with query:
Code:
SELECT Products.ID, Products.Product, Products.Active
FROM Products
WHERE (((Products.Active) Like Yes));
Then I changed some product 'Active' to "No"
And the old record disappearing.
How to make older record not disappearing when I filter some of field not being available to choose (because Active product uncheck)
More in-depth, Attachment Database1.zip
My form has a piece of code in it that deletes records older than two years :
Code:
Dim dteExpiry As Date
Dim strSQLDelete As String
dteExpiry = DateAdd("yyyy", -2, Date)
strSQLDelete = "DELETE from tblWaarschuwingen WHERE tblWaarschuwingen.datum2 <= #" & dteExpiry & "#;"
DoCmd.RunSQL strSQLDelete
But...
As it turns out it does not only delete records older then two years, but also records related with personnel number to that record !!
Now i do relate records by personnel number (its NOT the primairy key though) but in this case its not meant to do that !
How to build a query in Access 2010/13 that will not display any records that are older than 3 hours? See below for more specifics.
The criteria would be based on a field that houses the time (End Time) and also a field that houses a number (Status). So, only if the field in the record says 3 (based on the Status field) then it should not display the record after 3 hours (based on the End Time field) of being changed to a 3. Is it possible to do that?
I have attached a screenshot of what I am referring to.
I've built an an update query that updates a Yes/No field "Expired?" to "Yes" if the expiration date has passed i.e. if the current date is newer than the expiration date. I thought this would translate into the current date being greater than the expiration date?
UPDATE Alert
SET Alert.[Expired?] = 'Yes'
WHERE Alert.Current_Date>Alert.Expiration_Date;
When I run this, it identifies the records to be updated, but then stops updating the fields due to "a conversion failure."