Access Report

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.
urjudoAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Then check our spelling, ...This worked OK for me in this new sample
Database54.mdb
0
 
Patrick MatthewsCommented:
One easy way: have your report contain two subreports, one each for "Payment = Yes" and "Payment = No".  Place the subreport objects as desired.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
urjudoAuthor Commented:
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
 
urjudoAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
urjudoAuthor Commented:
Payment = SUPAYMENT (the SUPAYMENT is refer as Payment in the table), this field is store as either "Y"  or "N" in the table
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
urjudoAuthor Commented:
I did try this  =DCount("*","qryCollectAmount","SUPAYMENT= 'Y'"), it gave me #Error
0
 
Jeffrey CoachmanMIS LiasonCommented:
correct count and percent of Y and N
0
 
urjudoAuthor Commented:
is a different that I use a Query instead Table?  coz I use a Query on the report instead a Table
0
 
urjudoAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
urjudoAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
.
0
 
urjudoAuthor Commented:
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
 
urjudoAuthor Commented:
Thank you!!!
0
 
Jeffrey CoachmanMIS LiasonCommented:
ok
Great...
enjoy the weekend
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.