Avoiding Divide By Zero Error
Aug 25, 2005
Hi all.
I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.
I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.
Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:
Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)
Thanks for any help you can provide...
View Replies
ADVERTISEMENT
Sep 12, 2006
How can I avoid a divide by zero error in an MS Access Query?
View 2 Replies
View Related
Apr 10, 2013
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
View 5 Replies
View Related
Mar 14, 2008
I'm sure there's an easy solution to this somewhere...
I have a query that is supposed to assign a wage to a production job if the range output falls within a certain percentage range. For example, if they produce 125% to 130% of their goal, their wage is 15 cents higher. If it's between 130% and 135% of their goal, their wage is 30 cents higher. There are about 20 ranges that my bosses want set up. Short of making the longest Iif-Then-Else statement in history, does anybody have any suggestions? Should I use a separate table and somehow try to integrate it into the query?
View 14 Replies
View Related
Mar 13, 2006
Just wondering, if this is possible and if so how?
I got query results and would like to divide a figure from the number of results shown..
so if 4 results are shown from the query then i would like to divide 100 by 4, and if 5 results showed then divide it by 5?
View 5 Replies
View Related
Mar 17, 2005
Hi hope someone can help,
What I’m trying to do is work out price per quantity, at the mo I have a database that has products and price so lets take paint for an example.
If I have a 5 litre pot of paint and I’m selling it for £10 I would like a field that tells me the price per litre, so what I’m think I need to do is have a field called ”price” and divide it by a second field called “quantity” and output the sum to a third field called “price per litre” which populates the database.
Sadly I haven’t got a clue how to do this with access and if anyone can help it would be much appreciated.
Thanks
View 1 Replies
View Related
Oct 24, 2006
HiIn Excel, I believe you can format a number and automatically divide it by 1,000 without messing up rounding.For example, 10,000.06 after you apply the formatting becomes 10 as in 10k (for some reason accounting people find this easier to look at/work with). However, if you try to sum the value Excel will actually use the decimal figure rather than the number 10.Is there a similar way in Access?
View 2 Replies
View Related
Dec 8, 2006
Hi,
I have a text box which contains a formula. When the text box is divided by zero, it appears #ERROR in the text box. I would like it to be invisible when it divided by zero and to be visible when it has a number.
Any ideas?
Thanks,
Le
View 3 Replies
View Related
May 1, 2007
I have updated a database I created with additional reports, some UI
enhancements, etc., but have not changed anything about the table structure, field types, etc. The database has been in use for about a week by the user and I was working on my local copy. How do I provide the updated database to the user without incurring key violations on the auto-number PKs when their data is imported into the new version?
View 9 Replies
View Related
Feb 26, 2007
I wish to avoid the SumOf, AvgOf prefix in queries. I am aware that I can write sales:sales but in a query with 50 columns that is somewhat annoying.
Any help would be appreciated.
View 1 Replies
View Related
Mar 25, 2008
I have a query that is pulling data from a table containing a Tamestamp field. I want to set the query to pull my data but don't include anything that was logged in TODAY. How can I set this in my criteria?
Thanks
View 2 Replies
View Related
Mar 2, 2005
Hi there
I want to avoid duplicate dates in the date field of my form. It's a little more tricky than it sounds though. Imagine two fields in a form; one list box of eleven values and one date field. Once i have chosen a value from the list box i enter the date in the second field. It is ok for one value to have the same date as another value (in another record) but i don't want to have duplicate dates for one value. Any idea how to SIMPLY overcome this problem?
Not really looking to fully understand how it all works, i would be happy just to fix the problem.
Cheers
View 1 Replies
View Related
Aug 9, 2015
I am trying to divide each unique value in a column by the total column; so sales by total sales.
View 4 Replies
View Related
May 26, 2015
I have an alias named [Net Income]
[URL]...
I want to divide it's value by the sum of its values through another alias [%]. How to do it?
%:=[Net Income]/Sum([Net Income]) Not working
getting an error saying:
"You tried to execute a query that does not include the specified expression 'SR_NO' as part of an aggregate function."
View 4 Replies
View Related
Mar 28, 2006
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 3 Replies
View Related
Jun 5, 2005
Hi,
nuther question from the forum dunce I'm afraid,
I want to ensure the user of the form cannot enter the same [cboPlayerName]
twice for the same [txtMeetDate] (thanks for the advice trubolotta, I changed
the name of the [Date] field.) The user will select the PlayerName from a combo
box. Can I have the list in the combo box diminish by each player as they have
been selected for a [txtMeetDate] I suspect a monstrous amount of code &
intellectual input will be required to achieve this. Basically, what I'm asking
(this time !) is how can I have my program search my tblScratchScores for
duplicate names for the [MeetDate] that I am currently inputting Data for?
View 1 Replies
View Related
Mar 28, 2006
I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?
View 2 Replies
View Related
Sep 21, 2015
I have a modest sized data base and have been having trouble keeping out duplicate records with the same last name, first name, and DOB. I know I could use all 3 fields as a primary key, but I would like a simpler way of checking for duplicates.
View 3 Replies
View Related
Mar 22, 2014
I have a database which contains a table called "orders" that has a field called "quantity" in it. I also have a query called "defective parts supplied" which has a field called "defective parts".How would I use a new query called "% failure/defect rate per supplier" to calculate the following:100 * [defective parts]/[quantity].
View 14 Replies
View Related
Aug 1, 2014
If different people will be entering data into a "new donor" form, how do I keep them from adding the same person a second time?
It's possible people could have the same name, and it's possible that two people at the same address could be donors. So I assume I would want a "no duplicates" on the combination of name and address, right? But how to actually do that.
View 6 Replies
View Related
Nov 12, 2013
I have an order table
order nr quantity.
100 110
and a pack table
pack quantity
1 1
1 2
...
1 25
2 1...
I want to make a query that returns this :
order nr pack quantity
100 1 25
100 2 25
100 3 25
100 4 25
100 5 10
View 8 Replies
View Related
Apr 18, 2015
I have a list that shows how many books we have for each PO No. (Sorted by PO No.) Like below:
LIST 1:
Code:
PO NoTOTAL BOOKS
PO-0001 12
PO-0002 15
PO-0003 26
Now I would like to distribute/divide these different books to boxes and each box gets maximum 10 books. Like below list:
LIST 2:
Code:
BOX NOBOOKSPO NO
0001 BOOK 1~10 PO-0001
0002 BOOK 11~12 PO-0001
0002 BOOK 1~8 PO-0002
0003 BOOK 9~15 PO-0002
0003 BOOK 1~3 PO-0003
0004 BOOK 4~13 PO-0003
0005 BOOK 14~23 PO-0003
Format is not important in the list2, for example in Box 0002 we have book 11~12 means (book 11 of 12 +book 12 of 12) in po-0001
+ book 1 of 15 until book 8 of 15) in po-0002
books have bookno, for example for po-0001 we have 12 different books it means we have bookno (1 of 12) until (12 of 12) for this po no.
How I can produce automatically list 2 from list 1 by query or coding.
View 2 Replies
View Related
Jul 31, 2014
Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:
1 (hr) / 12 (pay periods) = 8.33, when it should be .0833
0.5 / 12 = 4.166 when it should be .0416
What is right is everything else, starting with 1.5 hrs
1.5 / 12 = 0.125
What do I need to do to make all of the values divide correctly?
View 3 Replies
View Related
Mar 17, 2013
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.
View 6 Replies
View Related
May 21, 2015
I have a function to write some data to a text file (as an activity log, separate to the live data contained in the DB)
It uses the FileSystemObject.OpenTextFile method and works fine
However, I'm conscious that, although perhaps unlikely, there is the possibility (which is enough for me to worry about it) that multiple users could trigger the function simultaneously, which could cause a conflict when multiple FE's try to write to the same log file at the same time.
So I'm trying to add some code to prevent this from happening, by checking if write-access is available before proceeding.
Below is the piece of code I've added. Was considering, as an alternative, looping until the objFile is no longer Nothing, rather than depending on the Err.Number?
Code:
Dim objFSO As Object
Dim objFile As Object
On Error GoTo ErrorHandler
...
Set objFSO = CreateObject("Scripting.FileSystemObject")
[Code] .....
View 3 Replies
View Related
Feb 15, 2013
I want to store currency amounts but not use the currency format. My DB is set to use all currencies and provide in all currencies.
Do I use the SINGLE data type?
View 1 Replies
View Related
