Solved

Access Report

Posted on 2015-01-22
21
88 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 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
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!

 

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

713 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