Queries :: Divide Profit Over Turnover To Show Margin - Division By Zero Error
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.
I am receiving the following error when printing different reports to several different printers in Access 2007: "The section width is greater than the page width, and there are no items in the additional space, so some pages may be blank. For example, the report width may be wider than the page width." According to my co-workers this issue began immediately following the upgrade to version 2007 from 2003.
I've worked out how to sum all my sales (turnover) from a given nominated date...but what I seek now is to project forward what the annual turnover will be (which will quickly give me a 'pulse' & indicate if I'm growing as my financial year progresses)
My financial year started on Feb 4 2014 ...I can easily sum all my sales from that date, but ideally what I'd like to do is divide that sum by the number of days elapsed (this will give the average turnover per day) & then project that forward to Feb 3 2015 (the last day of my financial year)
So is there anyway that access can work out the number of days that have elapsed between 4 feb & 'now' (to get the average) ....but more specifically also how many days between 'now' ....and Feb 3 2015 - I then can use the remaining number of days to multiply the average daily takins to give an indication of what my year end annual turnover will be :-)
Or am I faced with having to do such calculations manually outside of access?
I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.
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:
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?
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..
employee name joining date department section employee left (Yes/No) Left date
Now I am looking for;
i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start
ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)
iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise
iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)
I am trying to print out a report that displays the jobs(records) from the dates "User entry" to "User Entry" and then display the total profit at the bottom. I need help setting up the query or report to display the line items (each record) and the display the total at the bottom of the form between a user defined time period. (ex: 5/5/04 - 6/5/04) It is a "Gross Profit" report.
I have a field called ProfitToDate (currency) and a field called Profit. Each record will have a dollar/cent amount posted in the Profit field. I want the ProfitToDate field to be updated for each record. In other words, I want the ProfitToDate field to show the totaled amount of the Profit field for all preceding records and the current record. How do I calculate this in my form design? Please give an example and keep it simple. I’m new at this stuff.
Ok, so this one's been driving me batty for some time now. When I create a form in a new database, I can resize the design window to my heart's content, without any adverse effect to the form's rendered size. However, I also work with another database that was not created by me, and whenever I create a new form, the size of the form is entirely dependent on the size of the design window. This is extremely aggravating, as I have to pixel-by-pixel shrink the form renders the correct size. Is there an option somewhere in access or Form formatting to turn this "functionality" off, so that a form will only render the size that I designated to it, and not to its design window?
I'm trying to make a graph of Monthly profit and loss
I've got 2 tables: Invoices and Purchases.
Invoices contains the fields:
InvoiceTotal and InvoiceDate
Purchases contains the fields:
PurchaseTotal and PurchaseDate
I know I need to take one from the other to create my profit figure but what I can't figure out is how to incorporate the dates.
InvoiceDate and PurchaseDate might be different but both occurred in the same month, so I would like to sum my PurchaseTotal and InvoiceTotal during this month.
I'm trying to trouble-shoot an undocumented datbase built by someone else and running into a recurring problem. She has it set up where a macro runs several queries. When these macros are run, the queries cause "Division by Zero" errors which stops the macro in its tracks. The following is a query that causes this error, but by looking at this query, I see no place where a division takes place. I'm assumming that another porblem is occuring and Access just states that it's division by zero error when it's actually something else.
1) Has anyone seen a "Division by Zero" error when running a query when it's actually something else causing the problem? 2) Can anyone see from the query what could possibly cause this and give me a hint as to a good way to trouble-shoot these queries to possibly handle whatever problem is occurring.
Thanks,
The query is as follows: SELECT [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales AS [MTD NDS], [004c YTD Cogs by Category qry].SumOfNetSales AS [YTD NSD], [Buyer Category Fiscal Period for Report].EndDate FROM ((((((((((((BuyerCategory LEFT JOIN dbo_Category ON BuyerCategory.CategoryID = dbo_Category.CategoryID) LEFT JOIN [008c GMROI by Category] ON BuyerCategory.CategoryID = [008c GMROI by Category].CategoryID) LEFT JOIN [016c MTD GM by Category] ON BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON BuyerCategory.CategoryID = [018c MTD GPM by Category].CategoryID) LEFT JOIN [019c YTD GPM by Category] ON BuyerCategory.CategoryID = [019c YTD GPM by Category].CategoryID) LEFT JOIN [015c Turns Buyer Category] ON BuyerCategory.Buyer = [015c Turns Buyer Category].Buyer) LEFT JOIN [014c GMROI Buyer Category] ON BuyerCategory.Buyer = [014c GMROI Buyer Category].Buyer) LEFT JOIN [Buyer Category Fiscal Period for Report] ON (BuyerCategory.Buyer = [Buyer Category Fiscal Period for Report].Buyer) AND (BuyerCategory.CategoryID = [Buyer Category Fiscal Period for Report].CategoryID) GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate ORDER BY BuyerCategory.Buyer;
I have a field in the a query with the following: % OVER NB: 1-([Monthly MTA Table]!NB/[main inventory table]!PRICE) I then format the column in properties to percent When I try to put anything in the criteria I get an error "Division by zero"
I want to only the records that are greater then 80%
I have a query with the following calc. but it has an error of 'division by o' when I run it. Some of the figures may be null or 0 within the fields and I still want it to return a % figure result
I know its probably the iif function but am not sure how to use it in this instance.
Process %: Sum([Process volume]*[se]/[minutes worked])
I have a table named "Patients" with a field named "Code" and another field named "Patient" which contains the full name (LastName space FirstName) "Chaviatsos Nick" for example. I want to make an other table from the table "Patients" with three fields: 1. A field named "Code" 2. A field named "LastName" which will contain the Last Name of the field "Patient" of the table 'Patients" and 3. A field named "FrstName" which will contain the Frst Name of the field "Patient" of the table 'Patients" How Can I manage this?
Please help me with the round function. I want .5 to round to 1. Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.
I used the following expression: RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)
I've been using the following code to show Random records from an Access Database, the only problem with it is that every now and again it throws a "division by 0 error" any ideas how i can fix?
Code:strsql_videos = "select top 5 * from items ORDER BY RND(id)*(id*1000) MOD datePart('s', NOW()), id;"set rs_videos = server.createobject("ADODB.recordset")rs_videos.open strsql_videos, conn_videos, 3, 1, adCmdText
Iam hoping its possible with a few lines of code rather than some elaborate fix :P
So, I have some complicated calculations I am aliasing in my SQL query. My end goal would be to sort by my final calculation from all the other calculations I have performed but I am running in to some issues. I know I can't sort by referencing the alias itself without saying order by (column number). My calculations are usually pretty small (around .001 and up). Whenever I order by the column number I would like to use (select... from... where...order by 73) I get a "Division by zero" error in access. I figured this was an issue with how small the calculations were, so I multiplied by 1000000, then ordered by that column number. Still division by zero...I went ahead and changed all 0 values to 1, still division by zero. There are no zero values in the column itself, it's either 1 or something in the thousands. I can order by other columns, just not the one I want to use. I don't want to have to re-do my entire calculations in my order by clause.
