Results 1 to 3 of 3
  1. #1
    smylesb is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013

    Question Calculated Field Overflow When Dividing with Field Used Twice in Equation

    Aloha everyone! This is my first here on the Access forums - but if I continue using this software the way I have been - it may not be my last. I'll start by thanking everyone for reading and doing their best to solve my problem. Alright - here's my dilemma...

    I'm creating a basic database that will analyze financial data included on a firm's 10-K. I have been analyzing numbers throughout the semester and grew tired of manually calculating certain formulas. My intentions are to create a bunch of calculated fields that will do the work for me by relying on the data included on each sheet. I created a few tables, the ones at hand in this issue are "Income Statement", "Balance Sheet", and "Cash Flows". Each table has the relative financial data punched into it - and I intended on creating a query for each one. So far I'm stuck on the Income Statement Query. The purpose of the query is to calculate data using the information included in the relative sheet. For example, to determine a company's profitability ratio - you must divide their Net Income by their Revenue. I added all of the fields from the Income Statement into the query, and created the calculated field titled Profitability Ratio. This was input as [Net Income]/[Revenue] - I then formatted the field as a percentage and it did the work for me. Great! Now here's my problem: the next data I needed to gather is called the Interest Coverage Ratio. To determine a company's Interest Coverage Ratio you must perform this formula: (Net Income + Income Tax Expense + Interest Expense) / Interest Expense. As a pre-note - all of those fields are included (with data) on the table I titled "Income Statement". As all of the fields from that table are in the "Income Statement Query" - I didn't think I'd run into any problems. But here it is: I input the expression as [Net Income]+[Income Tax Expense]+[Interest Expense]/[Interest Expense] - and I get a message stating "Overflow". Furthermore, when I remove that final part of the expression (/[Interest Expense]) - the query runs fine and adds up all of the fields. All that I can theorize is that for some reason I am unable to use a field twice in an expression. Any suggestions? Thanks a ton guys. Aloha.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    A field can certainly be used more than once in an expression. Don't you want the sums to take place before the division? Not seeing parentheses. Even without the parens the expression should calculate.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    smylesb is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2013
    Well - you know what it was? I had initially had a "0" input for the divisor on the "Income Statement Table". I changed this - but it didn't update the associated query. I linked some ID's between my tables - and voila! I was no longer dividing by zero. Your tip about the parentheses really helped though. Even after my field read something other than a zero, it didn't want to divide so fluidly. I punched some parentheses around my sums and it worked perfectly. Thank you so much June7 - my first experience on this forum has been lovely. I hope that this newbie question can still assist others in the future. Go ahead and lock this one up as "Solved".


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-21-2013, 03:20 PM
  2. Replies: 3
    Last Post: 02-13-2013, 10:15 AM
  3. Replies: 4
    Last Post: 08-14-2012, 10:33 AM
  4. Overflow error - change field type in query?
    By Lady_Jane in forum Queries
    Replies: 6
    Last Post: 05-25-2011, 01:10 PM
  5. Calculated field based off another field
    By Bwilliamson in forum Forms
    Replies: 5
    Last Post: 05-09-2011, 02:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums