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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |