473,837 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Limit query returns and sums in a report

147 New Member
I have a column in a report that I want to return a sum of certain criteria from a query between two dates. I set the dates in a form that I launch the report from.
My query contains a field called ClearanceCode. The ClearanceCode field contains multiple entries of the following data: "A", "B" "C", "D","E", "F", "O", "U", "W". The column I am trying to fill is only one of 5 and the criteria is different for each column and therefore I can not limit the query to just the one columns needs.

I want the column in my report to show the sum of the amount of "A", "B", "C", and "W" entries in the query between the dates I enter in the form. I have tried the following expression:
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf(([qryMonthlyReport]![ClearanceCode]="A" And "B" And "C" And "W") And ([DateClosed] Between [Forms]![frmAnnualReport]![Date1] And [Forms]![frmAnnualReport]![Date2]),1,0))
However it is not returning the correct sum. It keeps giving me a sum of 4 when the real value should be 16. It appears to only be returning the sum of two of the clearance codes instead of all 4.
Dec 15 '07 #1
1 1819
puppydogbuddy
1,923 Recognized Expert Top Contributor
I have a column in a report that I want to return a sum of certain criteria from a query between two dates. I set the dates in a form that I launch the report from.
My query contains a field called ClearanceCode. The ClearanceCode field contains multiple entries of the following data: "A", "B" "C", "D","E", "F", "O", "U", "W". The column I am trying to fill is only one of 5 and the criteria is different for each column and therefore I can not limit the query to just the one columns needs.

I want the column in my report to show the sum of the amount of "A", "B", "C", and "W" entries in the query between the dates I enter in the form. I have tried the following expression:
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf(([qryMonthlyReport]![ClearanceCode]="A" And "B" And "C" And "W") And ([DateClosed] Between [Forms]![frmAnnualReport]![Date1] And [Forms]![frmAnnualReport]![Date2]),1,0))
However it is not returning the correct sum. It keeps giving me a sum of 4 when the real value should be 16. It appears to only be returning the sum of two of the clearance codes instead of all 4.
Try this. I am not sure what you intended with the 1 and 0 at the end of your expression, so I had to assume that if the expression did not result in a valid sum, you want it to return 0:
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([qryMonthlyReport]![ClearanceCode]="A" Or [qryMonthlyReport]![ClearanceCode]="B" Or [qryMonthlyReport]![ClearanceCode]="C" OR [qryMonthlyReport]![ClearanceCode]="W") And ([DateClosed] Between [Forms]![frmAnnualReport]![Date1] And [Forms]![frmAnnualReport]![Date2]),0)
Dec 15 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
5805
by: D. Dante Lorenso | last post by:
I need to know that original number of rows that WOULD have been returned by a SELECT statement if the LIMIT / OFFSET where not present in the statement. Is there a way to get this data from PG ? SELECT ... ; ----> returns 100,000 rows
0
1365
by: Len Coleson | last post by:
I am at wits end with this. I have a transaction report that has a union query as a record source that asks date parameters from an input form. The sub report is based on a variation of the same union but only has the "startdate" portion and it sums all records prior to whatever date I enter. Both reports have the same grouping/Sorting (MatNumber) I have a text box in the "MatNumber" header that picks up the information from the sub...
8
1747
by: Yisroel Markov | last post by:
I have the following in my code: strSQL = "SELECT tblTransactions.PshpID, tblTransactions.TransDate, " _ & " Sum(tblTransactions.DDAmount) AS SumDDAmount FROM tblTransactions " _ & "INNER JOIN tblMonthly ON tblTransactions.PshpID = tblMonthly.PshpID " _ & "WHERE tblTransactions.TransDate BETWEEN #12/31/03# AND #05/31/04# " _
9
10852
by: Terry E Dow | last post by:
Howdy, I am having trouble with the objectCategory=group member.Count attribute. I get one of three counts, a number between 1-999, no member (does not contain member property), or 0. Using LDIFDE as a comparison I get the same results. No members means just that, an empty group. Zero means that the DirectorySearcher.SizeLimit has been exceeded....
2
9164
by: someone | last post by:
How can I limit the number of records a Query retrieves or at least limit the number of records returned on a report. I only want to print the top 5 or 10 returns of a query into a weekly reort. Thank you -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final
1
1301
by: hylander | last post by:
My apologies for what to many of you will probably seem a simple problem. I have a number of queries which result in numerical answers. Is it possible to have access total these for a report. The database I am running uses Windows XP and NT - Office 2000 and 2003. The server it uses cannot have links with other Office applications. Thanks in anticipation.
1
1937
by: aaron.reese | last post by:
Guys, this is my problem:- I have records which are linked by two fields on a left inner join (there may be 0,1 or more records in the right hand table) The relationship is not unique (it's a map reference so it can't be...) so I am getting a cartesian join on the right hand table - look, it's not my database OK, I don't have any choice!!!
4
2540
by: dancole42 | last post by:
So I have an invoicing database based on two main forms: Orders and OrderLines. Orders has fields like: OrderID BillingMethod OrderDate CreditCard CCExp OrdSubTotal ShippingCharge
6
6881
by: Phil Stanton | last post by:
I am running a query that calls a function used to format addresses depending on the width of a control on a report that shows that address. The same query is used as the RecordSource of lots of similar reports, but all with different sized Address text boxes. For the function to work, the report need to be open in design view, so that the Text Box Width can be "measured". The function is obviously called for each line of the query (about...
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10575
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10628
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7004
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5670
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4043
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.