I have come across a problem with my database that I would appreciate some help with.
I have added to an existing table by creating a query with calculated fields (calculated from fields in the original table).
However, a few values in the calculated fields result in "error" or are null, this is due to the values in the original fields.
I have been given default values to substitute in for these error values, but I have found that I am unable to "edit" cells in a calculated field. (i.e. just trying to type a value in, replacing the "error").
Does any body know how I could go about putting these values in?
Or is it just a case that as they are known (not calculated), then they have to be included in the original table?
If so, how would I go about getting the calculated and non-calculated values in the same field?
I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database Dim C1 As Long 'For Total Cost Dim C2 As Long 'For Total Cost Dim C3 As Long 'For Total Cost Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code: Option Compare Database Dim C1 As Long 'For Total Cost Dim C2 As Long 'For Total Cost Dim C3 As Long 'For Total Cost Dim C4 As Long 'For Total Cost
Is there any way to edit the hyperlink field in my table, I'm thinking along the lines of an update query?
The database has thousands of records and the user has not typed any text into the 'Display Text' part of the hyperlink. This means that the stored hyperlink is double size because if no text is entered into the display text part then access uses the hyperlink string. This is a problem for me later on in the design.
I have tried a simple update query and this updates the display text as required but also loses the hyperlink itself, which I want to remain unchanged.
I hope I can explain this: I have a database with a table "Itinerary" it has two fields "Date of Visit" and "Agency Number" I have a second table that is linked from a different database "Agent Info"
I created a form based on the "Itinerary" Table then I put in a sub form to show the "Agent Name" and "Market Rep" from the "Agent Info" table.
The relationship is on Agent number in both tables. The user then adds the agent number on the form and the Agent name and Market rep automatically come in.
The problem is the user wants to be able to occasionally modify the info in the Agent Name and Market Rep fields. But does not want the new info to be changed on the original "Agent Info" table.
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.
I have a button in a form that needs to change the text in a field named idStatus from "Assigned" to "Returned"
I have tried using an expression like this "[Form]![Field]=12" in the OnClick spot but it returned an error. There is no "custom made" wizard for this and my VB skills are lacking.
Once again, thanks! (I know I post alot but this forum is amazing!)
Access 2013 stops working when I open any table in design mode, and select any field with the type "Number". I can edit Text, Date or Yes/No fields, but not Number ones.
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.
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
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.
