i would like to sum up 5 fields and save (or just show it in the form view is enough really) in a seperate field, i have managed to sum the 5 fields, but it summed up all 5 fields in all records, i'd like to have my form show the sum for the record, not the table.
it would be ideal that it would refresh when one of the 5 fields data changes, i.e from 1000 to 2000. again updating in the form view is what is needed.
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ
WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
Any way of setting up a table containing the following date/time fields.
StartDate
StartTime
EndDate
EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
I've tried many things (compact and repair, Nz(,0), sum()) and I can't get it to work.
I entered =[field1]+[field2] but it behaves like if I had entered =[Field1] & [Field2].
Yes instead of adding the numbers it just puts them together.
For example: Field1=3 and Field2=5 instead of field3=8 it shows Field3=35.
Hey All, I have a main form PID and and Building form I need to have the data from the building table display on the building form, upon adding a new record. Since my PID form and building form are linked to show the same records. PID Form contains- PIN NUM, ADDRESS, PARID. The Building form contains, PIN NUM, DATE, PHONE, OWNER, SIZE, LOCATION... I open my main form filter my results for my reocrds to show up... PIN-20053227, ADDRESS-MAINSTREET, PARID-NEWFIE22. This data is obtained through the PARID table. Now I click on the command button to see my building forms, there are 6 records, here I have the ability to add, modify, delete, save... What i want to do is add a new record, but have some generic fields appear from my table. since my name, PIN, address hasnt changed. So I would like to click add, and have appear from the building table, OWNER, PHONE etc... How might I be able to do this?? Thanks- NOTE: Hey all I double posted this incase you didnt notice this in the FORMS, Form.
View 4 Replies View RelatedHey All, I have a main form PID and and Building form I need to have the data from the building table display on the building form, upon adding a new record. Since my PID form and building form are linked to show the same records. PID Form contains- PIN NUM, ADDRESS, PARID. The Building form contains, PIN NUM, DATE, PHONE, OWNER, SIZE, LOCATION... I open my main form filter my results for my reocrds to show up... PIN-20053227, ADDRESS-MAINSTREET, PARID-NEWFIE22. This data is obtained through the PARID table. Now I click on the command button to see my building forms, there are 6 records, here I have the ability to add, modify, delete, save... What i want to do is add a new record, but have some generic fields appear from my table. since my name, PIN, address hasnt changed. So I would like to click add, and have appear from the building table, OWNER, PHONE etc... How might I be able to do this?? Thanks
View 3 Replies View RelatedI have two fields on the main form and I need a total count of records between the value of the two and then enter the total in a bound field on the main form
View 1 Replies View RelatedI have a simple table named [Groups] containing a Primary key ID, a Group name, a Group Leaders name and a date field. The date field merely lists the date of a particular Group related occurrence.
I need to know how many records have dates entered and assumed a simple total would achieve this. The Total is correctly shown in the appropriate row but most, but not all, of the date records are converted to show the same 'total' number.eg the date 28/01/2014 is changed to !4. Quite a weird occurrence.
Is it the case that date records cannot be totaled, in which case, why not all?
If I have a Report, with three fields (all data-type Text) named 'Jan', 'Feb' and 'Mar', and I want to have a fourth field (Unbound) alongside them, providing a count of the number of fields out of these three fields that are populated.
View 2 Replies View RelatedI have a very simple single-table database with 23 fields. Some of the records have only two or three fields populated. I would like to be able to print a summary of only the populated fields in each record.
It would Ideally look something like:
Record 1 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content - Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content
Field 21 Title: Field 21 content - Field 22 Title: Field 22 Content
Record 2 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content
Record 3 Name
Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content - Field 21 Title: Field 21 content
Field 22 Title: Field 22 Content
I have a table with multiple fields and each record in the table may not have data for all fields. I need to write a query that will pull only the fields that are populated for each record from the table.
View 1 Replies View RelatedI tried putting the following code in the form unload procedure, but although it does generate the correct message, it doesnt stop the form from closing.
Private Sub Form_Unload(Cancel As Integer)
If [Starttime] > 0 And [Admin_time] = 0 Then
response = MsgBox("Please click on the stop button to stop the clock")
Exit Sub
End If
end sub
If the above condition holds, I want the user to click on the stop button before they close down the form. I'm guessing that the form is already commited to closing before the unload event? :o
trying to identify if 2 fields within a table have been populated i.e.
Check date field and restaurant field, if these fields have been populated do
this
errStr = errStr & tmpDate & ", "
Else
validStr = validStr & tmpDate & ", "
[Code]...
I am looking to transfer a number of spreadsheets that I use to track IP Addresses to an Access Database, I have set up the tables and fields, what I would like to do is search for unused IP Addresses from populated table/fields.
View 2 Replies View RelatedI'm sub-novice when it comes to Access I'm trying to get calculated data in a field in a sub table to autofill in a field in a related table. So all of the data in 'total hours' column is calculated by the variable inputs in the 'hours' fields. Once there, I want it to feed itself into the 'total hours' field on the main table. Is that possible? Also - the 'total hours' field in the sub table won't calculate anything unless a value is placed in EVERY 'hours' field in the same sub table (e.g. 0).
View 14 Replies View RelatedHi,
This is my first post. Thank you for reading it. And thank you in advance for your assistance.
I have an invoice table and a payments table. One invoice can have many payments (a deposit, final payment etc). The relationship is created.
I have a form with tabs that hold subforms. One of the tabs contains fields from the Invoice Table (i.e. Invoice Number, Invoice Amount, Total Paid). One other tab has a subform that contacts fields from the Payments table (i.e. Invoice Number, Check Number, Check Amount). I would like to auto-populate the Total Paid field in the Invoice Tab with the SUM of the Check Amount from the Payments tab BY Invoice Number.
This doesnt seem to difficult but I'm having trouble with it. Any assistance would be much appreciated.
Thank you!
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is:
1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd.
2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
I have a number of tables in my database, and they're linked in with a form. I'd like to have a field in a form that's populated automatically based on what's inputted in a different field.For example, I've got one table with names and descriptions on widgets (name in column A, description in column B).
Widget 1 This widget is blue
Widget 2 This widget is green
Widget 3 This widget is yellow
When I'm in my form, I want when I select Widget 1 to have the corresponding text (This widget is blue) appear in a field below.
I have an order form that auto-populates details from the customer table into adderss and contact controls. But I don't want it to auto-populate when the underlying order table already has data in the related fields. Here is the code I use on Load form event...
======
Private Sub Form_Load()
'Use this version if the ID is a number
Dim x As Variant
Dim strControl As String
Dim lngID As Long
'If parameters exist, use them
If Len(Me.OpenArgs) > 0 Then
[Code] ....
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
I am going round in circles here; I have a form which has certain text fields that need to be populated before a user can click a command button named 'Record Movement' that opens up another form. What I want to do is disable the 'Record Movement' button until these fields are populated. I am using Access 2010.
View 6 Replies View RelatedI can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.
I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.
But how can I automate this to skip the dialogue box and just add it to the expression?
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
View 2 Replies View RelatedI have a form, combo box selection, when i make a selection, all the info is good on all my tabs, which holds the subform.My issue is, on one of the subform, it's a data entry form, where I want it to be on a new record page. instead it list all records. I want my users to see a blank page, but have one field populated. is this possible?
1) blank data entry page, but with a populated field?When a CU Name is selected,I want the (Enter Note) tab to go to a data entry or last page or new page.I have tried putting it to data entry form, but the CU Number will not populate.