Count the number of records which are listed in Report, Microsoft, Access, 2000, #Error message when

How I need to arrange my query and which calcualtion expression I need to enter into report design view to be able to count the number of records selected by this query?
Say, my query selected all records of not competed jobs which are older than 7 days. Now, how I'm counting in my report how many of un-completed jobs are left. How i do it?
raintAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Create a text box with this Control Source

=Count(*)

including the equals sign.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Or Create a text box with this Control Source
=1

and set the Running Sum property of the text box to OverAll

mx
0
NathanIrwinMining AnalystCommented:
Place a text box in the Report Footer Section and as stated above use something such as:
=Count([Date])
In the Control Source of the Text Box, where [Date] in an appropriate field from your query

Nathan
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
If your query has a filed [Status] with text like "Complete" or "Uncomplete", In the on open event of your report have:

Dim rs as dao.recordset
Dim intComplete as integer
Dim intNotComplete as integer

Set rs=Me.Recordsetclone
if rs.recordcount>0 then
rs.movefirst
do until rs.eof
If rs![Status]="Complete" Then
   intComplete = intComplete +1
Else
   intNotComplete = intNotComplete +1
End If
rs.movenext
loop
End If

  lblCompleted.Caption="Completed Projects: " & intComplete
  lblNotCompleted.Caption="Not Completed Projects: " & intNotComplete

rs.close

Also you need to have lables named lblCompleted and lblNotCompleted anywhere on your report.

Mike

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Mike ... I don't think Recordsetclone is available in a Report.

mx
0
Mike EghtebasDatabase and Application DeveloperCommented:
Mx,

Have you checked? Or, you are sure?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Mike - I'm sure.

Open a Report.
Go into the On Open event (or any event)

Type Me + Dot (Me.)

You will not see RecordsetClone in the Intellisense list.

If you go ahead and type it out
x = Me.RecordsetClone
the hit Compile - you will get a Compile error -"Member not found'

mx
0
Mike EghtebasDatabase and Application DeveloperCommented:
I have not checked it myself but I trust what your finding. Then, raint can try:

Dim rs as dao.recordset
Dim intComplete as integer
Dim intNotComplete as integer
                                                                v---- the same as your repor's recordsource SQL
Set rs=currentdb.openrecordset("Select * From Table1....")  '<-- this is the only change
if rs.recordcount>0 then
rs.movefirst
do until rs.eof
If rs![Status]="Complete" Then
   intComplete = intComplete +1
Else
   intNotComplete = intNotComplete +1
End If
rs.movenext
loop
End If

  lblCompleted.Caption="Completed Projects: " & intComplete
  lblNotCompleted.Caption="Not Completed Projects: " & intNotComplete

rs.close
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.