Count () in a report

hello, how can i do something like the following in a report:
i have a field with the possible following values: A L and T
i want to show a count of each in a text box in a report
the report would look something like:
There are X number of A's
There are X number of T's
There are X number of L's

count (*) where [fieldX]="A"
thanks in advance, charly
cazinkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use
=dcount("*","TableOrQueryname", "[FieldX]='A'")
=dcount("*","TableOrQueryname", "[FieldX]='L'")
=dcount("*","TableOrQueryname", "[FieldX]='T'")
0
cazinkAuthor Commented:
the record source was a statement so i changed to a query instead of the function referring to a table.
there are two fields i have tried:
MS:Left([MemberStatus],1)
[MemberStatus]

in the report in the report footer i have the following:
=DCount("*","qryDues Register_Paid","Left([MemberStatus],1)='A'")
i have also tried:
=DCount("*","qryDues Register_Paid","[MS]='A'")
both return an error
0
cazinkAuthor Commented:
the record source was a statement so i changed to a query instead of the function referring to a table.
there are two fields i have tried:
MS:Left([MemberStatus],1)
[MemberStatus]

in the report in the report footer i have the following:
=DCount("*","qryDues Register_Paid","Left([MemberStatus],1)='A'")
i have also tried:
=DCount("*","qryDues Register_Paid","[MS]='A'")
both return an error
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Then check your spelling closely.
You will get an error if you do not spell the name of your query *exactly* as it is in the navigation pane

Syntax like this worked fine for me:
="There are " & DCount("*","qryDues Register_Paid","MS='A'") & " number of A's"

sample db attached
Database74.mdb
0
cazinkAuthor Commented:
update and regroup, i copied your line and that does not work, however i do not think the syntax is wrong,
let me restate the problem, i have a group of members that pay dues, and the report groups on the amount of dues paid. in the grouping footer i have this line: ="Summary Total of Dues Paid and Donations Paid: " & Format(Sum([DuesPaid]+[DonationsPaid]),"Currency") this line will tell me how many members paid a certain dues amount. then this section repeats. What i am trying to accomplish is a listing in the report footer that looks similar to the following:
There are 12 members who paid $105
There are 42 members who paid $115
There are 23 members who paid $125
The line that we are working on is the same but would show the amount of type of member:
There are 12 members who are A status
There are 12 members who are T status
There are 12 members who are L status
i thought i was having a format issue with the dollar amount, so i went for the text line to help solve the error problem. these lines of code are in the report footer, is this the problem, if i reference something else i just get the grand total.
maybe i am trying to do something access is not designed for. the query has a parameter to answer when the report is opened, is this the problem.
thanks again for the help
0
cazinkAuthor Commented:
hello, update, if the query has a parameter question, the dcount() will not work. at least not in this context. the other flaw is that i know there are only three letter values A T and L. to rephrase what again i am trying to accomplish is to have a listing of how many paid amounts in a list in the report footer. i have them in each detail summary, but by putting them in the report footer is a recap of the above report.......ok new stuff, i embedded a report that gives me the list as i need, however the report now asks me for the answer to my parameter over and over. the parameter is looking for a year or just return for the current year. any thoughts on why this is happening
0
cazinkAuthor Commented:
and once more, it works perfectly if i do not have the parameter, always some little thing at the last moment,
0
PatHartmanCommented:
There may be only three values today but what about tomorrow?  It is always wrong to box yourself in like this with an unnormalized design when it is just as easy to do it right.

Select Status, Donation, Count(*) as RecCount
From YourTable
Group By Status, Donation.

This will produce the following list:

Status, Donation, RecCount
A, $105, 12
L, $125, 23
T, $115, 42
You can display this in a subreport in the report's footer as a recap.  If you want the list to sort as A, T, L rather than A, L, T, then you will need to assign a numeric value to sort by.  It doesn't have to be visible on the report, but it may need to be included in the detail line as a hidden field.

This method has no limit to the number of values you have.
0
Jeffrey CoachmanMIS LiasonCommented:
let me restate the problem
I picture is worth a thousand restates.
;-)

Just post an image of the report you have now, ...
Then post the *EXACT* output you require.

In your original post you did not mention anything about parameters or grouping...
0
cazinkAuthor Commented:
Pat, thank you for all your help, i agree totally with what you mentioned. one last thought, the table has values from previous years so the parameter question when the report opens asks for the year YYYY, or just leave blank for the present year. this works well until i embedded the second report. now the parameter appears multiple times when the report opens and if i scroll down to the end of the report where the embedded section is the parameter dialogue box appears again. any thoughts on this or is there a better way around this problem.
0
PatHartmanCommented:
You can't stop the multiple prompts in the report unless you use a form to enter the parameter (or a function or TempVar).  In case you hadn't noticed, the report will prompt for the parameter when you open it for preview and then again if you decide to print it.

I always run reports from a form so I have parameter boxes on the form and a button to run the report.  In cases where I have a report that must run from multiple forms, I use the where argument of the OpenReport method to pass the parameters.  If I only run the report from one place, I add the parameter to the report's RecordSource query.

Select ....
From...
Where SomeField = Forms!yourform!SomeField or Forms!yourformSomeField Is Null;

The "or ... Is Null"  allows me to have an optional argument.

In your situation, you will have to use the parameter in the query because the where argument of the OpenReport method will only work for a main report and you have both a main report and a subreport.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cazinkAuthor Commented:
i always believe that the answer to a question is just an answer, it is the reasoning of the solution that is real usable knowledge, thank you
0
PatHartmanCommented:
I agree which is why my answers frequently include "why".  The underlying "why" of this is that reports don't store the answer to prompts anywhere which is why they have to ask every time the query must run.  and that includes going from print preview to print.  Access reruns the query to ensure freshness for the data.  So, that is why the form solution works.  As long as the form remains open, the report can find the prompt value.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.