is there a way to display the results of a count query in a text box on a form?

Hello! I have a query that counts the number of records for a particular criteria... for example, it counts the number of records that have a date within 30 days of the current date. Is there a way to display that number on a form?

Do i use a text box? is a requery somewhere involved. This is my attempt at making metrics on my database.

Thanks!
jtovar3Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
In the Control Source of a text box, put this expression:

=DCount("*","YourQueryName")

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
See image
Capture1.gif
0
jtovar3Author Commented:
When I entered that, the only number it displays is 1 in the text box. When I run the query separately, it returns 17 (the correct answer). Is it something with number formatting?
0
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!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you post the SQL for the query?

mx
0
jtovar3Author Commented:
SELECT Count(tbl_Observation.Obs_ID) AS CountOfObs_ID
FROM tbl_Audit INNER JOIN tbl_Observation ON tbl_Audit.Audit_ID=tbl_Observation.Audit_ID
WHERE (((tbl_Observation.Due_Date)>DateAdd('d',-9990,Date())) AND ((tbl_Observation.Status)='In Progress'));

Open in new window


I put 9990 as the date just so i could count all the records and know for certain there should only be 17
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What is the name of this query and what exactly did you put in the Text box Control Source ?

mx
0
jtovar3Author Commented:
Query Na,e: Count_InProgress90DaysPast
in the Control Source: =DCount("*","Count_InProgress90DaysPast")

... I'm sure I have terrible naming conventions, but that is copied and pasted directly.
0
GRayLCommented:
My take is you have to use a Combo box.  In the Control Source property paste your query. Set Bound column to 1, in the format tab, no of fields1, field width say .5".
0
GRayLCommented:
Make that:  Row source type Table/Query
Row Source, copy of your Query
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
combo box ?  And you can't put a query in a Control Source.  You're thinking Row Source ?

jtovar3:  This should be working.
=DCount("*","Count_InProgress90DaysPast")

can you upload the db ?

mx

0
Jon JaquesInformation TechnologistCommented:
You could also create a public function in a module, which executes the query with ADODB, and then returns the record count.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What I posted should work.  Something else is going on here.

mx
0
Jon JaquesInformation TechnologistCommented:
What about just using =count(*) as the control source of the text box, rather than using dcount?
0
jtovar3Author Commented:
I can try to upload a sample database once I remove a bunch of the data from it. Will try and post in a few minutes
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... and please explain exactly how to demonstrate the issue.

thx.mx
0
jtovar3Author Commented:
Okay here is a sample of my database. If you go to the "Metrics" form, you can run the query by clicking the "90 Days" button under the In Progress column. That query displays 5 as the count.

The text box below, which is running the same query, only displays "1"

I hope that is detailed enough
AuditTrackerVersionTest2.accdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here you go ... sorry for the confusion:

mx
AuditTrackerVersionTest2-MX01.zip
0
jtovar3Author Commented:
Can you tell me what it is that you fixed because when i pasted

"=DLookUp("[Obs_ID]","Count_InProgress90DaysPast")"

into my original database, it came up with an error. "#Error"
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yes ... I had to alias that field in the query ... look at that field in the query and do the same thing ...
Otherwise ... it shows as Count Of .... when you run the query.
mx
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
jtovar3Author Commented:
Excellent! Thanks so much. Now I have to alias all the other queries but it will be worth it in the end.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yes, sorry ... I SHOULD have notice from the SQL I asked for that the query was using Count already, thus returning just one record (why you got 1) ... as opposed to all the records, in which case DCount ("*" ...) would have worked.

I always alias as such assuming the query will be displayed or used say on a form or report.

thx.mx
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.