I would like to have a table update query populate cells in a field, based on an IIf statement (below), but states that the IIf statement arguments still need to be enclosed in parentheses. I don't understand what the problem is, I have two sets of opening / closing parentheses.
Code:
IIf IsNull (=Mid( [CONTRACTOR_TASK]![TASK_NAME] ,6,6))
I would like to put a date in a form the region and then a staff code and based on the three entries then auto populate the remaining cells if those three have been entered together previously. Is this possible?
View 3 Replies View RelatedI am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.
Here are the conditions:
Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight
Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable
Good:
Lower Acceptable<AW<Upper Acceptable
The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.
Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..
Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
here is my criteria:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")
This does not seem to work. I have also tried:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")
I have a main screen that has a tabbed form in it. Each of those tabs has a sub-form in it that displays information and allows some information to be updated.
In one tab I have a query based sub-form that returns information related to the project. This information cannot be manipulated or changed as it comes out of a company managed database. I would like the analysts to be able to add more granular information to a new table by inputting information in a dropdown field for one of the new table fields, but utilizing two of the existing sub-forms fields as identifiers in the new table.
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies View RelatedI'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
View 3 Replies View RelatedI want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
I would like to achieve the following task described below:
Just for an example i have two tables = Table1 and Table2
Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_
I would like to update Price and Date Field of Table 1 where Table 1 ID matching with Table 2 and Table 2 Date is maximum(most recent date).
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
I have a simple SELECT CASE query. I'm not sure how the syntax goes and I want to learn about it.
In the attached file, if you click on "cohort table", you will see the categorization for each unit under "field1".
how will I be able to use a SELECT CASE statement in a query to populate each unit with assigned category?
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Hi all,
Simple question...I think. Is it possible to update values in a table, based on the results of a query?
For example, I have tblPRR and qryProcessed.
When I run qryProcessed, I would like the field "Status" in 'tblPRR' to update from 'No' to 'Yes'.
Could anyone advise if this is possible?
Cheers
Rob
Dear All,
This is my first post in the forum and would like to apologise for any shortcomings.
I want to update a field in my table with the result from a query.
Eg. Table A with two fields, a1 (recorda1) & a2 (recorda2)
Result from Query (recordq) has to be replaced in Table A, field a2.
after the code is run, (recorda2) should be replaced by (recordq)
Can anyone help me how to do this with the help of an update query.
Regards to all
Basheer
Hi all,
This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.
I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).
That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.
I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.
Can someone please post the solution asap. Thanks so much for your help.
Regards,
Mike J.
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
I have 2 tables.
tblOrderType
1 - Maintenance Order
2 - Breakdown Order
3 - Greasing Order
On my form I have two list boxes: An Order Type List box, and an Area Listbox.What query criteria or VBA code would I use so that I could populate a text box with the relevant order number based on the selections of the list boxes. i.e. MaintenanceOrder & Area Z would display MaintenanceOrderNoZ.
I have 2 tables called MakeTable1 and DBO_TBL_Activity
Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match
Below is the SQL i have so far
Code:
UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]
WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);
The error is :syntax error in update statement
It's an unusual request, I know, but it's necessary for my purpose.
I have a form, frmMain that I use to update a number of tables. On it is a number of combo boxs, comProduct, comBrand, comSize, comPrice and comQuantity. comProduct opens a new form, frmNew if the users input is not already in the table. On frmNew there are again combo boxes comProduct, comBrand and comSize. This form adds the record to the table.
So, if I input a new value - not already in the database - for product in the comProduct combo box, the form frmNew appears and I can proceed to define the product, brand and size of the new record. The form then closes and reopens frmMain and tabs across to comQuantity (skipping the fields I've already defined in frmNew).
Now if you can wrap your head around that, here is the problem: Because I've already entered the values for product, brand and size in frmNew, I want them to automatically appear in the appropriate combo boxs on the frmMain form. I've been using the following code (executed from frmNew, so Me = frmNew):
Code:[Forms]![frmMain].[comProduct] = Me.comProduct[Forms]![frmMain].[comSize] = Me.comSize[Forms]![frmMain].[comBrand] = Me.comBrand
It works, but I've since discovered it creates a new record in the table so I end up with two duplicate records (one when I submit frmNew and another when the values are automatically input into frmMain.)
Now, my question: How can I display these values in frmMain without having them create the second record? I was thinking of using a SELECT query somehow, but I don't know where to begin. I also considered using labels instead of comb boxes, but I'll need to be able to edit them if the record already exists.
I am trying to populate the text boxes in a form with data from a table based on the selection a user has made in a combobox. I am trying to do this through a DLOOKUP in the text boxes Control Source using the following:
Code:
=DLookUp("[upMobile]","tblRepName","[tblRepName].[repContactMobile] = " & Str([upRepName].[Text]))
in the form when viewing the data.
I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)
Here is my SQL :
Quote:
UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];
I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)
If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.
However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.
I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.
So I'm trying to populate a table based on selections I make within a form.
The form is based of a query that pulls a new product category that needs to be added to my first reference table for products.
The form shows all the new unique product codes, and there is a drop down box with product categories whose source is also the first reference table, so a new code would be:
'JBL - TRX - FVB - TRZ' And based on that new info, I would select from the drop down to select the corresponding category and click 'Add Record' button.
I'm building the event for the button and the code is as follows so far :
Private Sub Add_Record_Click()
End Sub
Private Sub cmbAdd_Record_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblPVMTable(PVMJoinField, SummaryPVMCategory) " & _
[Code] ....
I keep getting errors with the main portion of code.
Name of table fields :
PVMJoinField
SummaryPVMCategory
Name of Query Fields Populating the Form:
PVM_JOIN_FIELD
cboSummaryPVMCategory
I have two forms - Main and StudentAdvisor (filtered by student ID#) SAdvisor.jpg
The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.
On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).
The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)
Advisor Type 1=Initial, 2=Current, 3=Previous
Changed 1=Yes, 2=N/A
so a record would record as:
Initial and N/A
Current and Yes
Previous and Yes
I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.
also tried pulling form SA = Advisor to form Main = txtAdvisor
For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.
I am wanting to populate a control in a form based off of two different fields in one table. Is that possible?I have a table called tblEmployeeMaster and it has a LastName column and a FirstName column in which I need to have both first and last name show up in one box on my form?
View 6 Replies View RelatedI have 2 tables that hold similar data. In one table tblMultiSchedule I have a few blank columns. I want to update each column(ActualCost*) with the correct data which is stored in tblOrdersItems. the corresponding fields in each tbl are
tblMultiSchedule.OrderItemID1
tblOrdersItems.OrdersItemsID
I've been trying all day to have an update query to input the actual cost data into the tblMultiSchedule.
I know the rules regarding duplicate data but this is the only way i can do this.