query on report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created report with text box and control source is a query in created.
query is ...SELECT count(GRPOC)
FROM AuditData_Spring2005_Charter
WHERE GRPOC>0 And Schoolid=4;


I can run this query by itself and it runs fine. But in report...i selected
this query in control source...but displays only #Name?. I cannot figure
out how to use this query in report.

thanks
 
A query cannot be used as the Control Source of any object on a form or
report. You must either have the report or form bound to that query and then
bind the control to the desired field, or you must use an expression
containing a domain function (such as DLookup, DMax, DSum, etc.) as the
control source in order to get a desired value from a query.
 
Open your report in design view. Select the textbox in your report. Open
properties and go to the Data tab. Click in the control source property and
a down arrow will appear at the right. Click on the down arrow and select
CountOfGRPOC.
 
Ken Snell said:
A query cannot be used as the Control Source of any object on a form or
report. You must either have the report or form bound to that query and then
bind the control to the desired field, or you must use an expression
containing a domain function (such as DLookup, DMax, DSum, etc.) as the
control source in order to get a desired value from a query.
--

Ken Snell
<MS ACCESS MVP>






well..after i created text box..i did right click on it, went to properties, and in expression builder ..selected the query..still not clear on how to bind
Click to expand...
 
Please read what I posted again. You cannot use a query as the control
source of a textbox. You can bind the textbox to a field in the form's
RecordSource (in other words, use the field name as the textbox's
ControlSource).
 
ok..look can anyone give me step by step on how to bind my textbook

i did one and it worked fine..now cannot repeat process..

trying to use recordsource ????
 
i have 10 queries...report with 10 text boxes....have one working...looking
at field list have my source in there...but cannot repeat for 9 others
 
PCDatasheet indicated how you can select a field from your report's
recordsource as the controlsource of a textbox, thereby binding the textbox.

However, your report's recordsource will need to contain the field that you
seek to use. I'm not sure, from your posts so far, if this is indeed the
situation.

If the report's recordsource does not contain the desired field, then you
either must add that field to your report's recordsource, or you must use a
domain function (DLookup, for example) in an expression for the textbox's
controlsource in order to get the value from that field.

But, as you've not given us much information about the specific design/use
of your report, we can only keep repeating these general points for how you
would do what you seem to want to do. Sorry!
 
actually..i have been to other forums and they say to be as brief as
possible..but yeah....i have a query....9 queries ...i do a select
count...for 1st was able to somehow get expr2...name of result of query into
my RecordSource...and it worked fine......so.. yes..need to know how do I add
field to report's recordsource..

somehow i did it once..but cannot do for the 8 other queries..
 
Ignore what other forums tell you. Please back up and start at the
beginning.

What is the report's RecordSource right now? If it's a query, post the SQL
statement of the query. If it's a table, tell us a little bit about it.

Tell us exactly which field (and table) contains the data you want to
display.

I'm trying to understand if the field that you want to display is in the
report's RecordSource now, or do you need to get it from a table that is not
part of the RecordSource.

--

Ken Snell
<MS ACCESS MVP>
 
ok...i have 8 columns....aoc, boc, coc, doc thru hoc
there is also column with school id
they want of count of how many entries have a number 1 or greater
so i made 8 queries
when in deisgn view for query, used query--> sql specific--> data definition
and
put
SELECT count(aoc)
FROM AuditData_Spring2005_Charter
WHERE GRPOC>0 And Schoolid=4;
Click to expand...
when i run the query i get correct count..displayed as datasheet view with a
column named Expr2
(if i go to design view of query first column has Field: Expr2:Count(aoc)

In design view for report, have created textbox, rclick on properties, have
Control Source as Expr2 in Data tab.

When i look at properties for Report (chose Report in dropdown of properties
box), i have my query name as RecordSource. But i need to add 8 other text
boxes to report, each with it's own query. Cannot figure out how to tie all
8 queries to one report.

Sorry for this post, but have found no one who knows access. I used Data
Definition to write out my query. Come from heavy SQL background, but
struggling with Access..
 
So you want the count for each column when that column has a value of 1 or
more?

A query such as this should work for you:

SELECT (Abs(SUM([aoc]>0 and SchoolID=4))) AS aocCount,
(Abs(SUM([boc]>0 and SchoolID=4))) AS bocCount,
(Abs(SUM([coc]>0 and SchoolID=4))) AS cocCount,
(Abs(SUM([doc]>0 and SchoolID=4))) AS docCount,
(Abs(SUM([eoc]>0 and SchoolID=4))) AS eocCount,
(Abs(SUM([foc]>0 and SchoolID=4))) AS focCount,
(Abs(SUM([goc]>0 and SchoolID=4))) AS gocCount,
(Abs(SUM([hoc]>0 and SchoolID=4))) AS hocCount
FROM TableName;

Then use this query as the recordsource for your report.

--

Ken Snell
<MS ACCESS MVP>
 
oh my gosh..this worked..thanks
i thought i had to have a query for each textbox
they added more fields, so am just adding additional calculations
thanks

Ken Snell said:
So you want the count for each column when that column has a value of 1 or
more?

A query such as this should work for you:

SELECT (Abs(SUM([aoc]>0 and SchoolID=4))) AS aocCount,
(Abs(SUM([boc]>0 and SchoolID=4))) AS bocCount,
(Abs(SUM([coc]>0 and SchoolID=4))) AS cocCount,
(Abs(SUM([doc]>0 and SchoolID=4))) AS docCount,
(Abs(SUM([eoc]>0 and SchoolID=4))) AS eocCount,
(Abs(SUM([foc]>0 and SchoolID=4))) AS focCount,
(Abs(SUM([goc]>0 and SchoolID=4))) AS gocCount,
(Abs(SUM([hoc]>0 and SchoolID=4))) AS hocCount
FROM TableName;

Then use this query as the recordsource for your report.

--

Ken Snell
<MS ACCESS MVP>



cwr said:
ok...i have 8 columns....aoc, boc, coc, doc thru hoc
there is also column with school id
they want of count of how many entries have a number 1 or greater
so i made 8 queries
when in deisgn view for query, used query--> sql specific--> data
definition
and
put
SELECT count(aoc)
when i run the query i get correct count..displayed as datasheet view with
a
column named Expr2
(if i go to design view of query first column has Field: Expr2:Count(aoc)

In design view for report, have created textbox, rclick on properties,
have
Control Source as Expr2 in Data tab.

When i look at properties for Report (chose Report in dropdown of
properties
box), i have my query name as RecordSource. But i need to add 8 other
text
boxes to report, each with it's own query. Cannot figure out how to tie
all
8 queries to one report.

Sorry for this post, but have found no one who knows access. I used Data
Definition to write out my query. Come from heavy SQL background, but
struggling with Access..
Click to expand...
Click to expand...
 
Back
Top