Hi,
I've got a problem with the syntax of a calculated field. This is what I have so far:
TransportA: IIf([weightunitised]+[weightloose]<500,25,([weightunitised]+[weightloose])*0.05)
I want to add the following:
If [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])<500 minimum charge is 50 if [CollectionandDelivery] = Yes and ([weightunitised] + [weightloose])>500 then ([weightunitised] + [weightloose])*0.07.
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales and then adding Criteria like: EndDate = #10/07/2015# It doesnt work.
DEVICE| DISPLAY1 | DISPLAY2 _______________________________ A | A1 | A2 B | B1 | B2
Table 2 has 2 fields, but the second is a multi-value field
DEVICE | MDISPLAY _____________________________ A | **MUTI-VALUE** based off the table 1
In the row source of the Multi-value Field MDISPLAY, I have
Select DISPLAY1, DISPLAY2 from TABLE1
The problem. I can get the MDISPLAY field to display the items from Table 1, but it grabs ALL of them.I need it to display ONLY the DISPLAY1 and 2 field associated with the value of the DEVICE for the current row in table 2, which is 'A'
SO if I look at the row of the table 2 that has the device 'A', the MDISPLAY field for that row should have.Just A1 and A2, NOT A1,A2,B1,B2(all the rows). how to access / syntax of the current value of the DEVICE field in my row source.Select DISPLAY1, DISPLAY2 from TABLE1 where table1.Device = Table2.device /or Device ... etc.. 'doesn't work
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI A B C D 1 2 2 6 4 3
1 7 5 9 1 2 2 5 8 5 etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI A B C D 1 2 & % 2 6 4 3 1 7 5 9 1 2 & % 2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click() CurrentDb.execute "UPDATE TableI" Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2 Docmd.save Docmd.close End Sub
Hi, I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number: OrderNum: [OrderPrefix] & [PartNum]
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
i have opened a field (which was created in a table) in a form as a calculated fieled the calculations are ok but only in the for the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
I have this query which selects two fields and calculates it in another field. But the calculated field has more than 1 expression.Eg If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.
I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)
I have a query that I want to calculate the total hours the pupil has spent on activities.
The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.
I have a query with 4 fields: Pupil SumofDuration SumofPreparation TotalHours = ([SumofDuration]+[SumofPreparation])60
The problem I have is that TotalHours is calculating as an whole number. e.g. I have a Record Pupil = 701 SumofDuration = 270 SumofPreparation = 0 TotalHours = 4
But I want TotalHours = 4.5
I have set the Duration and Preparation field sizes to Decimal and Format to General Number.
I am trying to do the following in a form. 1) (Travel hours + work hours) * dollarperhr to get the "Total" bill to a customer.
2) The results will be put in a field "TotalDollars" that is a part of a table.
Please help.
I tried to put an event (on change) on the "dollarperhr" box so that after entering the values in "travel hours" + "Work hours" * "dollarperhr", the calculation will be done to populate "TotalDollars", but it didn't work.
I have a table named= Commun tasks which has 3 fields= [Commun tasks],[Quantity] and [Unit Cost]
I created from that table a form who shows these 3 fields. I also created in that form a calculated field (named Total) who shows the Total. I simply wrote in the control source =[Unit Cost]*[Quantity]
I have another table (Project Information) who has 3 fields=[Prior total],[New Total],[cost addition]
What I need to do is each time the [Quantity] or the [Unit Cost] are changed, I need to show the [New total]. But also i need to show the [Prior total] which is based on the [Quantity] and [Unit Cost] before the change. So i think i need to store the calculated field (Total) in a table, but I don't really know how to do that (I,m a newbie).
Ok let's simplify my question
How can I store in a table a value from a Calculated field? I know that we should not store calculated value but in my case I need to store them.
