General :: Populate Calculated Field Into Table
Aug 6, 2013
I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.
I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.
E.g. SK 456 849, SP 6789 4356.
I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.
Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).
I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?
View Replies
ADVERTISEMENT
Jan 15, 2015
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.
View 2 Replies
View Related
May 28, 2014
I have a table holding a list of post codes, and their servicing depot EG -
ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh
Then a form, which has a field for depot ( Fld_Depot )
What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode
Once the postcode is entered, it populates that field with the relevant depot from the table....
View 3 Replies
View Related
Sep 17, 2012
I'm new to access and working through a 'teach yourself' book. I want to maintain a control file that has the history of order numbers used (order_number, supplier, date_issued). I want to pick up the last record, add one to the order number, update with supplier and date, and then populate the order_number in my data entry form as well as add the new record to the control table?
View 2 Replies
View Related
Jul 18, 2013
I am trying to change the data type of a field in a table from calculated to something else. It gives me the error "this data type cannot be changed once the field has been saved"
Is there any work around to this?
View 2 Replies
View Related
Jan 17, 2013
I have a Table (tblFPAData) that is being populated with entries from a form I created. There are several fields but if I can get an answer for one relationship then I can repeat it for the others.
In tblFPAData there are the fields MaterialNo and Description.
I am creating another table called tblSchedule and using a form to enter the information. I need the user to select the MaterialNo from a combo (cmbMaterialNo) and the then table auto populates the Description from tblFPAData.
View 3 Replies
View Related
Apr 24, 2013
I have created a form that is based on a table.
The fields in the form are currently pull downs or manual enrty fields (all based on the columns of the table... "Bound" I think you call it).
I also have fields that I manually brought into my form as text boxes that I am using to populate a series of numbers. I have named them P1, P2, P3 etc. They are unbound fields.
I have a field that I am totalling the unbound fields. It is a column in my table. At one point the control source name was the table coulmn name.
I have changed the control source to read =[p1]+[p2]+[p3]+ etc.
The total does show up in the field on my form
HOWEVER.... When I save the data the total number does not get brought into the table. All of the other fields do.
View 5 Replies
View Related
Oct 24, 2013
I am wanting to populate a field by entering a date in another field. I am trying to determine age(years, months, and weeks) of something by entering a date in another field. Is that possible in Access?
View 1 Replies
View Related
May 26, 2013
I want to know the "cummulative sum of a calculated field".
Given Lookup table : A = U, V, W | B = X, Y, Z
Field C = iif([A]="U",10, iif([A]="V",20, iif([A]="W",30, 0)))
Field D = iif([B]="X",10, iif([B]="Y",20, iif([B]="Z",30, 0)))
Field E = C+D
Field F = Cummulative sum of "Field E". What would be the expression for the cummulative sum here ?
---------------------------------------------------------------------------------
I tried F = Sum(C+D), but it shows me error
---------------------------------------------------------------------------------
View 2 Replies
View Related
Nov 14, 2013
I have an invoice form named frmInvoice. It contains a subform named subInvDetails. I am trying unsuccessfully to transfer a calculated subtotal from a text box in subInvDetails to a total in a text box in frmInvoice. Is there a way to do this? I have tried everything I can think of but I can't seem to make it happen.
To be more precise, I have a textbox which is a calculated value ([Rate]*[Hours]) named txtSubTotal in the sub form. In the main form I have a field named txtTotal. I thought that setting the Default Value of txtTotal to =Sum([Forms]![subInvDetail]![Subtotal]) was the right way to it, but the value of txtTotal remained zero. I have also tried using =Sum([Forms]![subInvDetail]![Subtotal]) as txtTotal's Control Source, but that didn't fit my needs or seem like the right way to do it. It didn't work anyway.
View 3 Replies
View Related
Jun 20, 2013
So i have a Calculated field in a table which is an expiration date. It adds two years to another field. Both field are on a Form. make the calculated field editable because some of the expiry dates are not necessarily 2 years. How do i change this?? do i need to like code it? i have no clue how to even start.
View 1 Replies
View Related
Oct 16, 2013
I am looking to do a calculated field. Not sure what is the best way to do this, but I would like to have a dates in a table with all of our employees that has what date they started with the company. From that I want to know not how many days they have been with the company, but just the years.
So if I have been with the company 364 days, it should only show up as 0, and if I have been with the company for 365, it will read 1. I would like this to be in a query somehow. We use the number of years they have been with the company for their pay, and need it to be years, and not days.
View 9 Replies
View Related
Jun 16, 2015
I am working with access forms, but I am having a problem when I try to calculate fields. I have three text box:
Quantity
Price
Total
When I type numbers in quantity and price field the Total field should get populated with the total of Quantity and Prices..I have tried a lot of formulas like:
=[Quantity]*[Price]
But nothing happens when I put the form in Form View. In the Total field I get #Error.
View 1 Replies
View Related
Feb 13, 2015
I have Field "BC1Chng" which requires user input. I want to be able to write a code to reference to "BC1Chng" if there is an input in that field for any record...I want to copy the Remarks into each record in the Remarks Field.
I was able to get the remarks1 field to loop through each record copying what was in that field into each record.
Now I want it to look at the BC1CHng field and only copy to remarks1 field if there is any input in that field???
View 9 Replies
View Related
Mar 21, 2013
I'm trying to populate a field in my form from a query. I've been trying to figure it out from the web but can't get it sorted. I've got a combo box that selects a member of staff from a table, and then a query runs which takes the value from that combo to retrieve the staffs current rate of pay.
I've added this code to the on change event on the combo.
Private Sub cboStaffID_Change()
Me.txtunit.Value = [qrycurrentpay]![Unit]
End Sub
But it doesn't seem to work,
txtunit is the field I want to fill on my form, and Unit is he field name of the value I want from query qrycurrentpay.
View 2 Replies
View Related
Mar 24, 2014
I am trying to calculate the keystage and year group of pupils on my database. I have created queries that successfully calculate the data, but I cant work out how to add the results from my queries into an already created form. I thought I could use DLookup, but this just populates with a random value from the data and not the value for that particular pupil.
View 3 Replies
View Related
Jul 23, 2013
I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice
bidder is an id number that is reference to uid of table USER
the USER table have the following fields,
uid, uname, city, and state
I created a BID form for the user to enter data into the BID table. What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table.
I still have a second step where the user in the bid form can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.
iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.
iid, itemname, description
View 14 Replies
View Related
Dec 11, 2013
1. I have a table which lists all of the equipment we use in our company and would like to use it as a template in a form so that users can select a checkmark if the equipment is being used that day. The checked items would then be used to populate another table which records all of the equipment the user has selected. I thought the checkmark method would be best so that the user can scroll through the list of equipment and select multiple pieces for the day. The reason I don't use a multi-value field is so that the user can also enter quantity and hours of equipment used in the same table when they scroll through the list.
2. Once all of the equipment pieces have been checked, I would like the user to be able to click a review button which would navigate to the next form with all of the selections for the day. This new form will have the option to go back to the first form if any corrections need to be made. And also have the option to submit the results.
3. Once the user is finished with their selections, the user can then select Submit which will finalize the recorded equipment table and clear the checkboxes and any inputs from the equipment template table (without modifying anything) so that it can be used for the next day and so forth.
I am using access 2013...
View 3 Replies
View Related
Aug 23, 2012
I got Gross Income Query. But I have employees that have overtime rate as well
So I have to calculate using overtime Payment And Gross Income Separately
So I make query and in query . I write this equation
Ovetime Pay: IIf([Total Hours Worked]>[Normal Hours/week],([Total Hours Worked]-[Normal Hours/week])*[Overtime Payrate],"0")
I successfully calculate it ...
BUT IT JUST GIVE ME THE VALUE LIKE A NUMBER SUCH AS e.g. 50 ,150 but instead of that i want currency data type of that calculate field for e.g. 50 become "$ 50".
Okay so in able to do that i go to properties Sheet of the field of the query and change Format to Currency ..BUT IT DOESN'T CHANGE !!!
DATABASE PAYROLL - Copy.zip
View 2 Replies
View Related
Nov 9, 2013
So the information that I had to start with was
Colours
Silver
Black
Material
Metal
Plastic
Rubber
Digital
Component
Screen
Keyboard
Mouse Pad
Case
Hinge
So I've created 3 tables - one for each information group arranged like so. However my problems are - I don't know how to make it so that as soon as all the info for one set of parts is filled another set automatically appears? And in the Design section of the Form I have the following error because I wasn't too sure what to use apart from a text box-
View 7 Replies
View Related
Sep 6, 2014
On paper I have designed a faculty contact database and have now come to the point where I am designing the form. In the contact information table are Cell, Home, Office fields as well as Personal, Work, Alternate email fields which will contain the corresponding information. On the form after the information is entered the person needs to select an option from each of four combo boxes to indicate which is the preferred number, number for student, preferred email and email for students. The question is how to populate the text fields in a report based on the selection of the combo boxes with the information stored in the corresponding field (cell, home, office phone numbers and personal, work and alternate email). I am assuming it is done in the query but I don't know how or can it be done in vba?
View 14 Replies
View Related
Aug 5, 2013
I have a training log that has 4 tables, the employee table, the training course table, and the department table.
These tables all contain the names of employees, training courses, and department in one field and their respective ID codes in the other.
The 4th table is the actual log where the manager logs in who took the training courses. When the manager goes to select the employee name, course name, and department name is there a way not using VBA for the respective ID number to appear in the 4th table (they use the same field names and are related)
View 4 Replies
View Related
Jul 26, 2012
I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith"
Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:
=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.
View 2 Replies
View Related
Aug 18, 2012
Is it possible to have a calculated column based on another table. i have a 1 to many table. the table that is the parent hold a start time. all of the 'many' have an individual time length. i would like a calculated column in the parent table showing the full length. is it possible or will i have to use an update query?
View 1 Replies
View Related
Feb 28, 2013
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
View 8 Replies
View Related
Jun 18, 2014
Table1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
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
View 1 Replies
View Related
