?
Solved

Access Report

Posted on 2015-01-22
21
Medium Priority
?
107 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
  • 11
  • 9
21 Comments
 
LVL 93

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

609 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