A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I run a query that generates a count then display the resu.



 
 
Thread Tools Display Modes
  #1  
Old October 25th, 2004, 11:25 PM
58Starguy
external usenet poster
 
Posts: n/a
Default How do I run a query that generates a count then display the resu.

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


  #2  
Old October 26th, 2004, 01:33 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Mon, 25 Oct 2004 15:25:02 -0700, 58Starguy
wrote:

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "criteria")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
  #3  
Old October 26th, 2004, 04:45 PM
58Starguy
external usenet poster
 
Posts: n/a
Default

Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));



"John Vinson" wrote:

On Mon, 25 Oct 2004 15:25:02 -0700, 58Starguy
wrote:

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "criteria")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

  #4  
Old October 26th, 2004, 04:47 PM
58Starguy
external usenet poster
 
Posts: n/a
Default

Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));


"John Vinson" wrote:

On Mon, 25 Oct 2004 15:25:02 -0700, 58Starguy
wrote:

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "criteria")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

  #5  
Old October 26th, 2004, 04:51 PM
58Starguy
external usenet poster
 
Posts: n/a
Default

Thanks John but I used the query to create my record-set and the query has
two tables and a field in the related table is used in the query criteria. I
have tried pasting the SQL into this message but I keep getting and error
message when I try to send it.

"John Vinson" wrote:

On Mon, 25 Oct 2004 15:25:02 -0700, 58Starguy
wrote:

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "criteria")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

  #6  
Old October 26th, 2004, 04:53 PM
58Starguy
external usenet poster
 
Posts: n/a
Default

Trying message with pasted SQL but not asking for notification to see if the
message will through without the message nofification.
Thanks John but actually, I use the query to limit my record-set. The query
uses two tables and the query filter criteria is based on the related table.
So how should I proceed? Here's the query's SQL:

SELECT tblMain.fldDept, tblComputers.fldCPU, Count(tblComputers.fldCPU) AS
CountOffldCPU
FROM tblComputers INNER JOIN tblMain ON tblComputers.fldCompTag =
tblMain.fldCompTag
GROUP BY tblMain.fldDept, tblComputers.fldCPU
HAVING (((tblMain.fldDept)="CS - Storage") AND
((tblComputers.fldCPU)="C400"));


"John Vinson" wrote:

On Mon, 25 Oct 2004 15:25:02 -0700, 58Starguy
wrote:

I have a query that generates a count ie CountoffldSomething. When I open a
form, that is not bound to this query, I would like to run the query in the
background, display the results of the query in a text box on the form then
close the query. Can this be done and if so, how?


It's simpler than that. You don't need a query *at all*; just recast
the count expression into a call to the DCount() function. E.g. set
the Control Source of a textbox to

=DCount("*", "[tablename]", "criteria")

where "criteria" is the WHERE string from your totals query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

  #7  
Old October 26th, 2004, 10:27 PM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default

If this already a query saved in the query builder?

You can use:

Dim rstRecs As DAO.Recordset

Set rstRecs = CurrentDb.QueryDefs("YourQueryName").OpenRecordset

MsgBox rstRecs!CountOffldCPU

rstRecs.Close
set rstRecs = nothing


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
display query data on report Little pete Setting Up & Running Reports 6 September 30th, 2004 07:32 PM
Display Relational tables in a query Bill Duris Running & Setting Up Queries 1 July 23rd, 2004 03:37 AM
count individuals of a certain sizes in one query jw Running & Setting Up Queries 4 June 23rd, 2004 08:16 PM
Display a query result in form Le Tran New Users 0 June 15th, 2004 09:08 PM


All times are GMT +1. The time now is 06:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.