• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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!
0
jtovar3
Asked:
jtovar3
  • 10
  • 7
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In the Control Source of a text box, put this expression:

=DCount("*","YourQueryName")

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 ArchitectCommented:
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 ArchitectCommented:
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 ArchitectCommented:
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 ArchitectCommented:
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 ArchitectCommented:
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 ArchitectCommented:
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
 
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 ArchitectCommented:
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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now