Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding field together

Posted on 2014-12-11
27
Medium Priority
?
93 Views
Last Modified: 2014-12-17
I need to add field1 and feild2 into field3...is that better to make field3 a calculated field or doing it on the form.  If it's better on the form...where on the form??
0
Comment
Question by:Jass Saini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 4
  • +1
27 Comments
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40494182
I prefer to do simple calculations like this in the query.  Then I can bind the calculated field to the form and don't need to add code to make it update.

Select Nz(fld1,0) + Nz(fld2, 0) As fld3

Notice the Nz() functions.  If you are positive that your data will never contain null values then you don't need them.  Otherwise you do.
0
 

Author Comment

by:Jass Saini
ID: 40494189
So I am sorry where in the query should I do this
0
 

Author Comment

by:Jass Saini
ID: 40494200
Hello Pat,

Sorry.... I was using a query.  But I could find how to input into my blank fields, so now I am using a master/child form.  I need to make the calculation field in the subform
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 39

Expert Comment

by:PatHartman
ID: 40494302
using a subform doesn't change the suggestion.  Every form should use a query as its RecordSource.

If you insist on putting the calculation in the subform, add the following to the ControlSource for field3

=Nz(fld1,0) + Nz(fld2, 0)

The "=" tells Access that this is not a bound control.  The calculation will be updated if either fld1 or fld2 is changed.  Other solutions involve code.
0
 

Author Comment

by:Jass Saini
ID: 40494417
Ok..I have a query ....but it will not allow me to update or data input into the empty fields.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1000 total points
ID: 40494460
Here is a sample using a query
@PatHartman has the right of it
Ok..I have a query ....but it will not allow me to update or data input into the empty fields.

Open your query.
Can you alter fields there?
Then you have created a read-only query.
There are many causes of that.
The main ones are:
Multiple tables used that do not have direct realtionships
Primary key fields not included in the query
Unique records (DISTINCT) option in play.

Your query must be editable before your form will be!
CalculateControl.mdb
0
 

Author Comment

by:Jass Saini
ID: 40494528
So Pat...if I use    =Nz(fld1,0) + Nz(fld2, 0)  then it shows ...let's say fld1 is 74 and fld2 is 1...it shows 741 instead of 75
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40494538
Then your table may have a problem.
"74" + "1" results in 741
74 + 1 results in 75.

Are you storing numbers as text by accident, or on purpose?
=CLng(Nz(fld1,0)) + CLng(Nz(fld2, 0))
can bandage that over -- but if you aren't storing numbers as text for a reason, then you should fix the table and use Pat's formula
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40494553
As Nick suggested, the fields are defined as text which is why they concatenated rather than summed.  CLng() will convert them to Long Integers but that may or may not be the correct data type.  What data type are you expecting?
0
 
LVL 85
ID: 40495130
Ok..I have a query ....but it will not allow me to update or data input into the empty fields.
If those "empty fields" are your calculated fields, then you won't be able to update them.
0
 

Author Comment

by:Jass Saini
ID: 40495205
No not calculated fields...I may have found the problem.  Is it okay to import a excel sheet and use that as a table???
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40495212
Is it okay to import a excel sheet and use that as a table???
Import it as a new table, and it becomes and Access table and all is well
Link to it, so it remains an Excel sheet and then there are problems.

MS lost a patent case to a Guatemalan gentleman.
They have not purchased or leased his patents
Instead, they broke Access's ability to update linked Excel sheets to not violate his patent.
Data on a linked Excel sheet is read-only in Access
0
 

Author Comment

by:Jass Saini
ID: 40495221
I made a table using a excel file.  I tried running that table with another table for a query..query is fine.  But when I make the form the blank fields..I can not enter in data.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40495230
You downloaded and look at the sample I posted here, right?

It had a table, form and query that shows how it all works.
Post a sample mdb file of your own if you can and wish.
0
 

Author Comment

by:Jass Saini
ID: 40495240
I clicked on it...but can't access it...  Here you go
Database10.accdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40495242
Ah,

EE has a bug at the moment.
for .accdb files you must right-click and SaveAs | All files and then give it the .accdb ending.
I'll have a look.
0
 

Author Comment

by:Jass Saini
ID: 40495255
Do you want me to send it again
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40495287
Ok,

Here is your file returned.
I have altered table Final_table
I have altered Query1
I have altered form Final_table.

I have created the 4 calculated fields/controls that I think you wanted
I have put them all on the form and arranged them
Calculated fields generally are not stored in the table.  I have removed them.
Query1 had no join.  I added it
Query1 does the calculating, and not the form

As noted above, for .accdb, right-click and SaveAs | All files | database10 v1.accdb
Database10-V1.accdb
0
 

Author Comment

by:Jass Saini
ID: 40496406
Thank you for helping....but for some reason when I right click it does not give me an option to do a SaveAs
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40496517
With IE 11, it is certainly an option.
Here it is on a OneDrive Link
0
 
LVL 85
ID: 40500049
.but for some reason when I right click it does not give me an option to do a SaveAs
If you're trying to download the file Nick posted, then you may just try clicking on it directly. I can do that, and it downloads fine on my machine.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40500787
In IE 11, accdb files try to open as html on a left-click.
Have since July.  Bug report filed, but the issue is not fixed.  Reported in the Glitches group too
Right-click | SaveAs | All files | name it *.accdb works.

But not a left-click
0
 
LVL 85
ID: 40501333
Ahhh ... I'm using Chrome and FireFox. Those browsers behave differently, it would seem
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40501372
Yup,
Chrome, it works ok for.
Although boag200 runs Chrome without Quicktime and he couldn't run this attachment.
IE 11 without Quicktime would.
mapV3.mdb
0
 
LVL 85
ID: 40501467
I can download that one on my machine, using Chrome. Not sure if QT is installed or not.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40501474
Boag2000 could download it.
When he opened it without IE as the default browser it threw all sorts of errors.
It's got a WebBrowser control that displays Google Static maps with pins
0
 

Author Closing Comment

by:Jass Saini
ID: 40505345
Thanks..
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question