How To Use Calculated Field In Next Field's Source
Nov 5, 2005
I am using a function to calculate the number of days in a quarter. I have the Control Source for field "A" set to
=CalcQtr1Days([AdminDate]). I am calling CalcQtr1Days and passing the AdminDate. It calculates the number of days I'm looking for and returns that number in my field. Then, I want to use that number, along with DaysAbsent, to calculate DaysPresent. So, in field "B" Control Source, I have =CalcQtr1Days - DaysAbsent.
This gives me #Name? in field "B". I've also tried =DaysPresent = CalcQtr1Days - DaysAbsent but doesn't work either. What is the syntax for using the value calculated in field "A" in my calculation for field "B"?
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.
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.
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS: ID_PRODUCT (primary key, autonumber long integer) ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS: ID_ORDER (primary key, autonumber long integer) FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.) FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen) CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS] from PRODUCTS where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)
Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.
Hi, I know this has to be simple, but when I test a Data Access Page on my local machine I get an error that says "data source field is read only". I've made these pages before and never had a problem with writing to the database using a DAP. The database is not read only. Any ideas?
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.
How to Add a calculated field named CustomerName as the first column that is either the company or the concatenation of the last name, a comma a space, and the first name. Sort in ascending order by Customer Name ?
I've came up with CustomerName: IIf([CompanyName] , [LastName] & ", " & [FirstName])
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
