Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Issue adding calculated fields into a subquery.

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-09-17
I am running into an issue where on each of my subreports on my main report, I need to create two calculated fields:  [% of Total AR], and [% of Total (Unbilled)].  I tried to add these two fields into the main table (not a problem).  Then I went into the main (non-append query:  "qry_Project Manager_HISTORY" and add these two calculated fields, but was unable to do so.

The error I received after adding the two calculated fields then trying to run the main rpt was:
"Subqueries cannot be used in the expression (tbl_ProjManager_History.[AROver75]/[TotalAR]).

The fields I was trying to add, were the:
Sum(([UnbilledOver45]+[ UnbilledOver30])/([TotalUnbilled])

I am not able to do this and can't understand why.  What I am doing wrong?
Question by:bkthom
  • 3
LVL 12

Expert Comment

ID: 38406470
Are those fields available within the scope of the sub query?
LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 38406532
You get that error because in your query, I had revised the names to match the original names since I had mistakenly thought you would not be using them in a calculation.  So when you use [TotalAR] in your calculated field, it thinks you are referring to the summation rather than the field in the table.
queryThere are 2 ways you could fix this.  Either revise the name in the query such as you see in the image above where I renamed it to xTotalAR.  You would need to update your subreports as well with the new name.
Or you could include the table in the field reference like so:
tbl_ProjManager_History.[AROver75] / tbl_ProjManager_History.[TotalAR]

A problem I see though is if your TotalAR is 0, you would end up with a divide by zero error.  So you would need to add a condition like so.
IIf (TotalAR=0, 0, AROver75 / TotalAR)
LVL 29

Expert Comment

ID: 38406554
By the way, you don't need to add the fields [% of Total AR] and [% of Total (Unbilled)] to the table.  They should just be calculated each time in the query.

Author Closing Comment

ID: 38406603
IrogSinta -

YOU ARE GREAT!  That works.  Thanks for the quick follow up.

I have to run into a meeting but hit another area that I have a question on.  I don't have time to post a question but when I get out of this meeting (at the latest two hours) I will post it.  It has to do with the field [MonthDelqSum] and how I can just get a sum "1" for a PM that has a Delinquency for a given month rather than summing up ALL the number of projects a PM has delinquent for any given month.  That is the very general idea of my issue.  But like I said, I will post my question in about two hours.

MANY, MANY, thanks again for all of your assistance!
LVL 29

Expert Comment

ID: 38406626
@Jared_S, looking at your stats, it looks like you're quickly rising to be another good source for answers here.  In this situation though, I had the advantage of being involved in the previous post found here.

A hearty welcome to EE!  I wish you well.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 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