How To Set Criteria For Archiving Data Using Form-based Parameters
Dec 4, 2012
I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.
A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.
But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).
My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query....
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
currently on my db it stores data on various projects, and these projects are sorted by a status of on hold, on going, or finished. What im trying do is move only the projects that are finished but still keeping a record of them so we can view them in the future.
i was thinking maybe i could move the finished projects into another db? but not sure how to do, or is there a better way to achieve this?
Hi, I have a form which is made up for 3 tables and I am trying to create an append query for each one in order to keep records of data before it is updated. The append queries seem to work but they append all data rather than just one selected record. I know I will next need to create a macro which can be used each time a record has been updated and a copy is sent to the archive. Can anyone help me with this, or have any useful suggestions?
At certain times, I want to archive employees out (lets say they are terminated). When I do this, something strange happens. If an employee has 4 records in the servicedata table and 4 records in the Classdata table, then it exports out 16 records (4 x 4). I would expect it to export out 8 records.
I'm trying to create an archiving routine as my database is becoming very large. For about 10 tables I want to shift certain records to an external database which would have the required 10 tables with the same table names and structure.
So far so good. I now want to automate everything using vba. I can see how to use the INSERT INTO statement but I don't want to have to name every field as there are hundreds. I just can't see how to do this.
If the table structures are identical how do I neatly shift a bunch of records from one to the other using code.
Ok. Here's the situation. I have query setup. This query has 2 columns: 1. Agent Name 2. Sale Value
I have 2 tables. Table1: Includes Sales for this pay period only Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need: I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above. Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
I'm not even sure which forum to put this in, as it involves a form, two tables, some queries, and possibly a macro. I've poked around the message boards here and haven't quite found a method that works for my situation. So here we go!
I have a recruiting database that I've developed for some other users in the office who are not Access builders. They have asked if there is a way to archive old records into another table for safekeeping, which I can do through the database window, but which they probably couldn't. I want to give them the ability to tuck these records away as needed, and without them having to ask me to do so every time.
The main table in the DB is called tblPosition. There is a form called frmPositionManagement which is bound to this table. It's through this form that the recruiters make all their updates and whatnot to records stored in tblPosition. I also have a table called tblArchive which I have created using the exact same fields as tblPosition so I can store the outdated records there.
I'm looking for a programmatic way to do this. I was hoping to put a button on frmPositionManagement that would let the user remove that record from tblPosition and send it to tblArchive. I had figured on creating a macro (mcoArchive) that took the following steps:
* MsgBox "Are you certain you want to archive this record?" * Run an Append Query to add the selected record to tblArchive * Run a Delete Query to remove the record from tblPosition * Close the Append Query * Close the Delete Query
Then I started building those two queries and the macro, and the wheels fell off my brain.
The difficulty I'm having is getting the system to say to itself, "The record currently displayed on the frmPositionManagement is the one I need to append and delete." I have a specific record selected on the form, so how do I pass that record's unique ID through to the two queries to make this record the one which is appended/deleted?
I had tried setting the criteria for the unique key in the Append/Delete queries to =[frmPositionManagement]![AutoID], but that just led to a pop-up box that asked me to input frmPositionManagement!AutoID, which is not what I wanted at all.
My VB isn't all that great, so I was trying to stick to macros, but if someone has an idea for a programmatic solution for this conundrum, I'd appreciate hearing it!
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code: DoCmd.RunSQL DelSummarySQL
Here is my full code
Code: Private Sub cmd_Delete_Click() Dim DelSummarySQL As String Dim StartRange As Date
I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].
I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?
What I have is:
IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)
Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?
I have a query thats outputs my costs on a project divided into 50 categories. Additionally, each project can be divided into 3 stages, each with the same 50 cost categories. I have been using a query that prompts the user which stage they would like to look at, and it works great.
However, it would be useful to be able to see all stages at once, next to one another.
So, essentially, i want to be able to input the stage parameter in the criteria box for three fields in the query, so instead of showing the costs for one stage at a time, it will show all three(and a summation across all stages), without a prompt.
It works when i set the one field manually to the first stage, but when i add additional fields, it returns a blank query.
I need to open my form with a filter attached that is based on criteria already existing in the subform. I've tried putting the following code in the OnOpen event of my main form : Code:Dim stDocName As StringDim stLinkCriteria As StringstDocName = "Campaign Form"stLinkCriteria = "[Campaign - Last Contact Status subform].Form![Communication Response] in ("2","4","10","11")"DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormPropertySettings, acWindowNormal
Basically I want to open my main form (Campaign Form) and only show records where the "Communication Response" is 2,4,10 or 11(a variable on my subform "Campaign - Last Contact Status subform"). Which in itself is a problem because I can't seem to code all of these variables- it will only accept one.
When I run the above code I am getting asked to enter a parameter value for "Campaign - Last Contact Status". I'm not too sure where this is picking this up from or how to fix it. I've even tried to run this based on the underlying query of "Campaign - Last Contact Status subform" with the same results.
Can someone help me? All I want to do is open the form based on the criteria I specify which is contained in a field in the subform. It doesn't sound that hard but it seems to be getting the better of me!
Hi all, I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID ChartNumber PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.
[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"
Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!
Oh, and for what it's worth... If, [calcDestZip5]'s value in QueryWizard form = "55422" Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
I have the following code which i found on another thread on this forum (thanks to original author) which is attached to the On Click of a button which prints the report corresponding to the details displayed in the form.
Dim strCriterion As String Dim strMsg As String, strTitle As String Dim intStyle As Integer
This works fine, however, i need to be able to select the report based on more than 1 criteria. For example, the Reference Number can be repeated but is distinguishable from each other by an Issue Number i.e. ReferenceNumber = 93, Issue 1 or 2 etc. At present when i run the above it prints all versions of, in this case, reference number 93, which given that each report is only a single page isn't a show-stopper but it would be nice to have it working as i would like.
I have tried adding to the strCriterion line such as strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber] and "[IssueNumber]=" & Me![IssueNumber] but no joy. I have tried bracketing the whole line and variations thereof, again no joy.
Can anybody tell if what i am attempting to do is possible and if so how do i go about it?
I have tried the above coding using MasterID which is the Autonumber PK but it produces an "Enter Parameter Value" box for MasterID. Obvioulsy if i can get it to work for the Autonumber then my problem goes away but i can't seem to figure out why it works for Reference Number (Number) and not MasterId (Autonumber)?
What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
I have a Master form which includes tabs within the tab I have subforms. I am looking for a way to filter my master form based on data found within my sub form.
I have a check box within one of the sub-forms called "softwareInstall" if yes the box would be checked. I want to filter all customers within my main form with a check box in my main form to turn on and show all customer who has this box check within the subform SoftwareInstall=True.
I have a report with a table as the row source. I have command buttons that opens different forms and allows the user to choose criteria, the form then filters the report based on the chosen criteria in the form, but if I use the destination city form to filter the report by destination city, then filter the report using a different form, the destination city filter is lost, is there a way to filter the report with a form by say destination city, then further filter that dataset with another form for say location city.
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
I have a fact table which contains a list of products at many different Retail Prices. I want to band these products into groups based on Retail Price Bands.
I have created a second table with the fields Retail Price Band, Minimum Retail Price, Maximum Retail Price. This defines the banding structure.
I would like to join Retail Price with Retail Price Band based on the parameters in the second table but don't know where to begin.
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
