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 » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT Statement in a Report



 
 
Thread Tools Display Modes
  #1  
Old November 30th, 2006, 08:33 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

What's wrong with this code? It keeps giving me the ?Error message, and when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.
  #2  
Old November 30th, 2006, 09:21 PM posted to microsoft.public.access.reports
Klatuu
external usenet poster
 
Posts: 7,074
Default SELECT Statement in a Report

Where in the report are you trying to use this query?

"chickalina" wrote:

What's wrong with this code? It keeps giving me the ?Error message, and when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.

  #3  
Old November 30th, 2006, 09:22 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default SELECT Statement in a Report

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a reserved
word in Access, but you are probably OK in the query. Elsewhere the use of
the word Value could cause naming conflicts since most controls have a value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message, and
when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.



  #4  
Old November 30th, 2006, 09:31 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

I'm trying to use it in the Footer to calculate the page totals.

"Klatuu" wrote:

Where in the report are you trying to use this query?

"chickalina" wrote:

What's wrong with this code? It keeps giving me the ?Error message, and when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.

  #5  
Old December 1st, 2006, 06:33 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

John,
Thanks for the help... I'm going rename Value to QtrValue or something like
that... but for now, the code still does not work. Might that be because of
the placement on the report? It's in the footer now to give apply this
equation to each section.
I've tried with the brackets and without.
Thanks.

"John Spencer" wrote:

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a reserved
word in Access, but you are probably OK in the query. Elsewhere the use of
the word Value could cause naming conflicts since most controls have a value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message, and
when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.




  #6  
Old December 1st, 2006, 07:20 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

John,
Also, I have a form that looks like thiswhich is only supposed to be a 5
year projection)

Country
Local
2007 2008 2009 2010 2011 Total
Idea 5 5 5 5 5 25

State
Idea 4 4 4 4 4 16

The yearly totals are taken from a Crosstab Query, but the years are hard
coded.
If I want 2012 included, I have to go back into the query and add that
column. How can this be done automatically?

Because I want to create a pop up form for the report where they can put a
start and end date so the report only shows the 5 year window. How do I set
up the report fields?

OR, is this too involved?

P.S. this is also the same report where I'm putting the SELECT statement in
the footer.

"chickalina" wrote:

John,
Thanks for the help... I'm going rename Value to QtrValue or something like
that... but for now, the code still does not work. Might that be because of
the placement on the report? It's in the footer now to give apply this
equation to each section.
I've tried with the brackets and without.
Thanks.

"John Spencer" wrote:

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a reserved
word in Access, but you are probably OK in the query. Elsewhere the use of
the word Value could cause naming conflicts since most controls have a value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message, and
when
I try to run the report, it says there's a problem. It runs in the query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.




  #7  
Old December 1st, 2006, 09:10 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default SELECT Statement in a Report

What do you mean the query is in the footer? Queries are record sources for
reports.

Are you trying to use a query as a control source? If so, you can't.


"chickalina" wrote in message
...
John,
Thanks for the help... I'm going rename Value to QtrValue or something
like
that... but for now, the code still does not work. Might that be because
of
the placement on the report? It's in the footer now to give apply this
equation to each section.
I've tried with the brackets and without.
Thanks.

"John Spencer" wrote:

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a reserved
word in Access, but you are probably OK in the query. Elsewhere the use
of
the word Value could cause naming conflicts since most controls have a
value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message, and
when
I try to run the report, it says there's a problem. It runs in the
query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.






  #8  
Old December 1st, 2006, 09:18 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default SELECT Statement in a Report

You seem to be asking multiple questions at once. I would suggest that we
try to solve one question at a time.

1) Post the SQL of the crosstab query for specific advice on how you might
modify it to get just five years. If you want to vary the number of years
then we will hope that Duane Hookom is around and can point you to some
sample databases on how to do this.

2) Instead of hard coding the years, use relative column names like Year1,
Year2, Year3, Year4, and Year5. You can use code on a form or in a report
to change the captions on labels or the control source of unbound text
controls to show the year numbers.

3) If you have the start year and you are always going to have 5 years, you
don't need to ask for the end year. Plus if you are limiting the report to
5 years and the user enters 2005 to 2014 they are going to be confused when
they only get 2005 to 2009 data in the report.

Are you doing this on a form or are you doing this in a report?


"chickalina" wrote in message
...
John,
Also, I have a form that looks like thiswhich is only supposed to be a 5
year projection)

Country
Local
2007 2008 2009 2010 2011 Total
Idea 5 5 5 5 5 25

State
Idea 4 4 4 4 4 16

The yearly totals are taken from a Crosstab Query, but the years are hard
coded.
If I want 2012 included, I have to go back into the query and add that
column. How can this be done automatically?

Because I want to create a pop up form for the report where they can put a
start and end date so the report only shows the 5 year window. How do I
set
up the report fields?

OR, is this too involved?

P.S. this is also the same report where I'm putting the SELECT statement
in
the footer.

"chickalina" wrote:

John,
Thanks for the help... I'm going rename Value to QtrValue or something
like
that... but for now, the code still does not work. Might that be because
of
the placement on the report? It's in the footer now to give apply this
equation to each section.
I've tried with the brackets and without.
Thanks.

"John Spencer" wrote:

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and
field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a
reserved
word in Access, but you are probably OK in the query. Elsewhere the
use of
the word Value could cause naming conflicts since most controls have a
value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message,
and
when
I try to run the report, it says there's a problem. It runs in the
query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.





  #9  
Old December 2nd, 2006, 04:27 AM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

I am trying to do this in a report. Let me see if I have this correct:

1. I do not need the crosstab query I've created.

2. for the "name" for the 5 years will be Year 1, Year 2, etc. and they will
be in the page header. and take the year from the calculations in the detail
section.

3. The details section will hold the actual calculations for every idea,
with a select statement, by year.

4. In order to calculate certain characteristics (some ideas, some are cash,
some are both) at the end of each country, this can be done with a select
statement in which section... this is what I don't get. I've tried the code
in the details section, page header, page footer, you name it. It doesn't
work. I checked and rechecked the code.

So, if I am correct... where do I find all this glorious code!

Thanks for your help John, I appreciate it!
M

"John Spencer" wrote:

You seem to be asking multiple questions at once. I would suggest that we
try to solve one question at a time.

1) Post the SQL of the crosstab query for specific advice on how you might
modify it to get just five years. If you want to vary the number of years
then we will hope that Duane Hookom is around and can point you to some
sample databases on how to do this.

2) Instead of hard coding the years, use relative column names like Year1,
Year2, Year3, Year4, and Year5. You can use code on a form or in a report
to change the captions on labels or the control source of unbound text
controls to show the year numbers.

3) If you have the start year and you are always going to have 5 years, you
don't need to ask for the end year. Plus if you are limiting the report to
5 years and the user enters 2005 to 2014 they are going to be confused when
they only get 2005 to 2009 data in the report.

Are you doing this on a form or are you doing this in a report?


"chickalina" wrote in message
...
John,
Also, I have a form that looks like thiswhich is only supposed to be a 5
year projection)

Country
Local
2007 2008 2009 2010 2011 Total
Idea 5 5 5 5 5 25

State
Idea 4 4 4 4 4 16

The yearly totals are taken from a Crosstab Query, but the years are hard
coded.
If I want 2012 included, I have to go back into the query and add that
column. How can this be done automatically?

Because I want to create a pop up form for the report where they can put a
start and end date so the report only shows the 5 year window. How do I
set
up the report fields?

OR, is this too involved?

P.S. this is also the same report where I'm putting the SELECT statement
in
the footer.

"chickalina" wrote:

John,
Thanks for the help... I'm going rename Value to QtrValue or something
like
that... but for now, the code still does not work. Might that be because
of
the placement on the report? It's in the footer now to give apply this
equation to each section.
I've tried with the brackets and without.
Thanks.

"John Spencer" wrote:

It looks as if you have ther brackets incorrectly placed.

SELECT [tbl_Ideas_Bank].[IdeaID]
, [tbl_Ideas_Bank].[BenefitType]
, [tbl_Quarter].[QtrEndDate]
, [tbl_Quarter].[Value]
FROM [tbl_Ideas_Bank], [ tbl_Quarter]
WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

And since you really don't need the brackets becuase your table and
field
names contain no spaces. That could be rewritten as
SELECT tbl_Ideas_Bank.IdeaID
, tbl_Ideas_Bank.BenefitType
, tbl_Quarter.QtrEndDate
, tbl_Quarter.Value
FROM tbl_Ideas_Bank, tbl_Quarter
WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND
tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007#

I would be wary of using "Value" as a field name, since it is a
reserved
word in Access, but you are probably OK in the query. Elsewhere the
use of
the word Value could cause naming conflicts since most controls have a
value
property.

"chickalina" wrote in message
...
What's wrong with this code? It keeps giving me the ?Error message,
and
when
I try to run the report, it says there's a problem. It runs in the
query
though.

SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType],
[tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value]
FROM [tbl_Ideas_Bank],[ tbl_Quarter]
WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND
((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#)));

Thanks.






  #10  
Old December 3rd, 2006, 03:46 AM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Maybe my note was cryptic?

I'll try again...

Page Header : Title of the report. Labels for Year 1, Year 2, etc.

CountryID Header: This is so the report sorts by Country, but the Country
name is in the Page header so it repeats at the top of the page in case there
is more than one page in each country.

Structural Header: This is the second sort level.

Details: Idea Description and the Totals for each idea by year.

Structural Footer: Totals by year for all the ideas in that country.

Page Footer: Page number and current date.


I am trying to do this in a report. Let me see if I have this correct:

1. I do not need the crosstab query I've created, I should use the tables
that store the information.

2. for the label name in the page header I should use for the 5 years: Year
1, Year 2, etc. and the actual years would appear when a start date is typed.
I should use the "Select" code in a text box "row source" forthe calculations
in the detail
section. The determining start date will be entered in a "pop up" form
before the report is open. This will also be code.

3. I would like to see at the bottom of each country a total by
[benefittype]: some ideas are cash, some are ETR and some are both. I need
these three line items at the bottom of the page per country. This can be
done with a select
statement, but in which section? This is what I don't get. I've tried the
code
in the details section, page header, page footer, you name it. It doesn't
work. I checked and rechecked the code.

So, if this is correct, what code should I be using?

I hope this is more clear then before and you can help.

Thanks for your help John, I appreciate it!
M

 




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


All times are GMT +1. The time now is 05:43 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.