Solved

Access Report

Posted on 2015-01-22
21
90 Views
Last Modified: 2015-01-23
Hi Experts,
I'm working on a report in Access 2010.  I have a question about the report.  This report has a field "Payment" is a Yes/No field.  for example, I have 50 records in this report that have either the payment is Yes or No, so all 50 will show Yes, No on each record, how do I do if I want all "Yes" in on left side of the report and all "No" on the right side of the report and get the percentage too.  Does any one have a sample of this kind of the report?

Thank you.
0
Comment
Question by:urjudo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
21 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40564550
One easy way: have your report contain two subreports, one each for "Payment = Yes" and "Payment = No".  Place the subreport objects as desired.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564609
You did not specify if this report had any "grouping"

If I am understanding correctly, ...you can get a count of yes and no with something like this:
Put a textbox in the report footer.
Name it: txtCountYes
Make the cobntrolsource something like this:
=Dcount("*","YourTable","Payment=-1")

Put a another textbox in the report footer.
Name it: txtCountNo
Make the cobntrolsource something like this:
=Dcount("*","YourTable","Payment=0")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564658
Sorry, I did not read your post clearly.
If you need the percent yes/no, see the attached sample...(the report is named "Table1")
Database54.mdb
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:urjudo
ID: 40564820
Hi Jeffrey,
I like your idea, but when I put the text box and on the controlsource, I put
=DCount("*","qryCollectAmount","SUPAYMENT= Y") it gave me "error" not a count, did I do anything wrong?

Thanks,
0
 

Author Comment

by:urjudo
ID: 40565012
my query has parameters, I change to =count([SUPAYMENT]="Y")  but it gave me the wrong count.  I have one "Yes" and one "No" but when I use this :
Total of Yes:  =count([SUPAYMENT]="Y")
Total of No:   = count([SUPAYMENT]="N")
and I got both "2", it should be
Total of Yes:  1 not 2
Total of No:   1 not 2
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565220
You stated that this was a yes/No datatype:
This report has a field "Payment" is a Yes/No field.
Yes/No fiels in Access set Yes=-1 and No=0
...so when you say that you are doing this:
=DCount("*","qryCollectAmount","SUPAYMENT= Y")
It implies that the actual stored value is "Y" or "N"
If so, then try your expression like this:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
?

So lets be clear
What is the Yes/No field defined as in the table?

JeffCoachman
0
 

Author Comment

by:urjudo
ID: 40565245
Payment = SUPAYMENT (the SUPAYMENT is refer as Payment in the table), this field is store as either "Y"  or "N" in the table
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565253
Just FYI
The Count() function does not accept criteria,
So you can really only do this:
=Count(YourField)
If you need to specify a Where argument (SUPAYMENT="y")
Then you have to use dcount:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565258
Payment = SUPAYMENT (the SUPAYMENT is refer as Payment in the table), this field is store as either "Y"  or "N" in the table

Then as I previously stated, this syntax should work:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
0
 

Author Comment

by:urjudo
ID: 40565260
I did try this  =DCount("*","qryCollectAmount","SUPAYMENT= 'Y'"), it gave me #Error
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40565311
Then check our spelling, ...This worked OK for me in this new sample
Database54.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565317
correct count and percent of Y and N
0
 

Author Comment

by:urjudo
ID: 40565333
is a different that I use a Query instead Table?  coz I use a Query on the report instead a Table
0
 

Author Comment

by:urjudo
ID: 40565344
if I removed the parameter in the query, then it works.  but if I have a parameter for the date range then it does not work.  do I need to entered anything for the date range?  my parameter in the query is Between [begindate] and [EndDate]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565347
No difference
Make sure you have the spelling of the query correct.
...and make sure that "SUPAYMENT" is actually in the query and is spelled correctly as well.
0
 

Author Comment

by:urjudo
ID: 40565373
again, without the parameter in the query, it works but once I  put the parameter back to the query, then it gave me "#Error" for the count.  I think it's the parameter problem but I  have to have the parameter in the query.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565381
if I removed the parameter in the query, then it works.  but if I have a parameter for the date range then it does not work.  do I need to entered anything for the date range?  my parameter in the query is Between [begindate] and [EndDate]
This is all new information...

In order to get this resolved a little quicker, ..can you post a small sample database that exhibits this behavior?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565413
.
0
 

Author Comment

by:urjudo
ID: 40566552
Hi Jeffrey,
Thanks so much for your help!!!  It finally works because I open the report from the form instead run the report from the report view, that's cause the error count.  Thanks again.
0
 

Author Closing Comment

by:urjudo
ID: 40566555
Thank you!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40566841
ok
Great...
enjoy the weekend
;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 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