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
  #21  
Old December 7th, 2006, 12:28 AM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Is this enough information, or do you need more?
Let me know... Thanks.

"chickalina" wrote:

Here is the table information:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
BenefitType (ETR Only, Cash Only, or ETR &Cash)

tbl_Quarter
IdeaID (dual primary key with Qtrenddate)
QtrEndDate (this is the Quarter end Date)
QuarterValue (These are the values to calculate with)


"John Spencer" wrote:

If you are using a label then you have to set the label's caption.

for 3.
lbl_Year1.Caption=forms![frm_customreports]![ControlNameon
frm_customreports] +0

As for 4, you really need to post the SQL statement. so we have some idea of
the column names,etc. I keep thinking that you are trying use a query
directly as the source for a control. If you are that does not work.

What columns do you want to Add up horizontally? Across the page can
probably be handled in the reports source query, or with vba in the report's
detail section.


What columns do you want to add up vertically? Depending on what you
specifically want to do here, this could involve a sub-report or just some
calculated controls on the report.

As for using the form for input into the query. Here is a Simple Select
(not a crosstab) example

Parameters Forms!frm_CustomReports!StartYear Short;
SELECT a,b,c,d
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)

A crosstab might look like the following and it would if I got all the
syntax correctly give you a count of c for each year and a grand total for
the year for each combination of a, b, and d.
Parameters Forms!frm_CustomReports!StartYear Short;
Transform Count(c) as CountEm
SELECT a,b,d,
, Sum(C) as ThisLineTotal
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)
GROUP BY a, b, d
PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In
('Year0', 'Year1', 'Year2', 'Year3', 'Year4')

"chickalina" wrote in message
...
Disregard Previous....
I meant that I know that you are all helping... I didn't realize you were
volunteers...
It's late...

3. I put the VBA code in the header and changed it to:

lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0
txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0
etc.
etc.
etc.

I can't figure out what to put here... it's a form where you pick the year
start from cboYear and click on the Preview Report button... the above
code
is in the header section... the first label is lbl_Year1. Can I use a
label?
or should I make it a text box...?

And 4: What do I use for the select statement in the Control Source for
totaling for the year by Idea in the detail section?
the query is [qry_STRAPforReport Query] and the field names are
"qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would
pull the year from the same screen where you pick the start date? so it
would
collect all fields ending in 2007?

And then... the characteristics is another field in tbl_ideas_bank that
needs to be subtotaled on the bottom.... separate from the year total. I
guess I mean it would be broken down by the three choices from a combo box
for 2007 for each country.

I hope this makes better sense.

Thanks again.




  #22  
Old December 7th, 2006, 07:13 AM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:

Is this enough information, or do you need more?
Let me know... Thanks.

"chickalina" wrote:

Here is the table information:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
BenefitType (ETR Only, Cash Only, or ETR &Cash)

tbl_Quarter
IdeaID (dual primary key with Qtrenddate)
QtrEndDate (this is the Quarter end Date)
QuarterValue (These are the values to calculate with)


"John Spencer" wrote:

If you are using a label then you have to set the label's caption.

for 3.
lbl_Year1.Caption=forms![frm_customreports]![ControlNameon
frm_customreports] +0

As for 4, you really need to post the SQL statement. so we have some idea of
the column names,etc. I keep thinking that you are trying use a query
directly as the source for a control. If you are that does not work.

What columns do you want to Add up horizontally? Across the page can
probably be handled in the reports source query, or with vba in the report's
detail section.


What columns do you want to add up vertically? Depending on what you
specifically want to do here, this could involve a sub-report or just some
calculated controls on the report.

As for using the form for input into the query. Here is a Simple Select
(not a crosstab) example

Parameters Forms!frm_CustomReports!StartYear Short;
SELECT a,b,c,d
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)

A crosstab might look like the following and it would if I got all the
syntax correctly give you a count of c for each year and a grand total for
the year for each combination of a, b, and d.
Parameters Forms!frm_CustomReports!StartYear Short;
Transform Count(c) as CountEm
SELECT a,b,d,
, Sum(C) as ThisLineTotal
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)
GROUP BY a, b, d
PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In
('Year0', 'Year1', 'Year2', 'Year3', 'Year4')

"chickalina" wrote in message
...
Disregard Previous....
I meant that I know that you are all helping... I didn't realize you were
volunteers...
It's late...

3. I put the VBA code in the header and changed it to:

lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0
txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0
etc.
etc.
etc.

I can't figure out what to put here... it's a form where you pick the year
start from cboYear and click on the Preview Report button... the above
code
is in the header section... the first label is lbl_Year1. Can I use a
label?
or should I make it a text box...?

And 4: What do I use for the select statement in the Control Source for
totaling for the year by Idea in the detail section?
the query is [qry_STRAPforReport Query] and the field names are
"qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would
pull the year from the same screen where you pick the start date? so it
would
collect all fields ending in 2007?

And then... the characteristics is another field in tbl_ideas_bank that
needs to be subtotaled on the bottom.... separate from the year total. I
guess I mean it would be broken down by the three choices from a combo box
for 2007 for each country.

I hope this makes better sense.

Thanks again.




  #23  
Old December 7th, 2006, 02:52 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default SELECT Statement in a Report

If you are still looking for a method to display the results of a crosstab
of moving window of years, look at the dynamice monthly crosstab report
solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to
easily change from month to year.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:

Is this enough information, or do you need more?
Let me know... Thanks.

"chickalina" wrote:

Here is the table information:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
BenefitType (ETR Only, Cash Only, or ETR &Cash)

tbl_Quarter
IdeaID (dual primary key with Qtrenddate)
QtrEndDate (this is the Quarter end Date)
QuarterValue (These are the values to calculate with)


"John Spencer" wrote:

If you are using a label then you have to set the label's caption.

for 3.
lbl_Year1.Caption=forms![frm_customreports]![ControlNameon
frm_customreports] +0

As for 4, you really need to post the SQL statement. so we have some
idea of
the column names,etc. I keep thinking that you are trying use a
query
directly as the source for a control. If you are that does not work.

What columns do you want to Add up horizontally? Across the page
can
probably be handled in the reports source query, or with vba in the
report's
detail section.


What columns do you want to add up vertically? Depending on what you
specifically want to do here, this could involve a sub-report or just
some
calculated controls on the report.

As for using the form for input into the query. Here is a Simple
Select
(not a crosstab) example

Parameters Forms!frm_CustomReports!StartYear Short;
SELECT a,b,c,d
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)

A crosstab might look like the following and it would if I got all
the
syntax correctly give you a count of c for each year and a grand
total for
the year for each combination of a, b, and d.
Parameters Forms!frm_CustomReports!StartYear Short;
Transform Count(c) as CountEm
SELECT a,b,d,
, Sum(C) as ThisLineTotal
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)
GROUP BY a, b, d
PIVOT 'Year' & (Year(SomeDateField) -
Forms!frm_CustomReports!StartYear) In
('Year0', 'Year1', 'Year2', 'Year3', 'Year4')

"chickalina" wrote in message
...
Disregard Previous....
I meant that I know that you are all helping... I didn't realize
you were
volunteers...
It's late...

3. I put the VBA code in the header and changed it to:

lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0
txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0
etc.
etc.
etc.

I can't figure out what to put here... it's a form where you pick
the year
start from cboYear and click on the Preview Report button... the
above
code
is in the header section... the first label is lbl_Year1. Can I use
a
label?
or should I make it a text box...?

And 4: What do I use for the select statement in the Control Source
for
totaling for the year by Idea in the detail section?
the query is [qry_STRAPforReport Query] and the field names are
"qtrendddate" and "quartervalue". I'm guessing, the SELECT
statement would
pull the year from the same screen where you pick the start date?
so it
would
collect all fields ending in 2007?

And then... the characteristics is another field in tbl_ideas_bank
that
needs to be subtotaled on the bottom.... separate from the year
total. I
guess I mean it would be broken down by the three choices from a
combo box
for 2007 for each country.

I hope this makes better sense.

Thanks again.






  #24  
Old December 7th, 2006, 02:55 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default SELECT Statement in a Report

Sorry, I haven't responded but I've been busy and have not been able to take
an extended period to examine your problem. At this point, I just don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a select
statement) in the footer of the report. That makes no sense to me. You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem. In the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:



  #25  
Old December 7th, 2006, 03:10 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Hi John,
I have a report that "should" show the yearly sum for an idea for 5 years. I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for each
of three different fields (all ideas fall into one of the three). I created
a crosstab query that gives me yearly sums, but then you can't break that
down into the three sections, and I can't figure out a way for a user prompt
for the start year.

Should I be using a Select Query for the source and then SELECT statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able to take
an extended period to examine your problem. At this point, I just don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a select
statement) in the footer of the report. That makes no sense to me. You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem. In the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:




  #26  
Old December 7th, 2006, 03:14 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Is there a way to do this in a report?

"Duane Hookom" wrote:

If you are still looking for a method to display the results of a crosstab
of moving window of years, look at the dynamice monthly crosstab report
solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to
easily change from month to year.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:

Is this enough information, or do you need more?
Let me know... Thanks.

"chickalina" wrote:

Here is the table information:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
BenefitType (ETR Only, Cash Only, or ETR &Cash)

tbl_Quarter
IdeaID (dual primary key with Qtrenddate)
QtrEndDate (this is the Quarter end Date)
QuarterValue (These are the values to calculate with)


"John Spencer" wrote:

If you are using a label then you have to set the label's caption.

for 3.
lbl_Year1.Caption=forms![frm_customreports]![ControlNameon
frm_customreports] +0

As for 4, you really need to post the SQL statement. so we have some
idea of
the column names,etc. I keep thinking that you are trying use a
query
directly as the source for a control. If you are that does not work.

What columns do you want to Add up horizontally? Across the page
can
probably be handled in the reports source query, or with vba in the
report's
detail section.


What columns do you want to add up vertically? Depending on what you
specifically want to do here, this could involve a sub-report or just
some
calculated controls on the report.

As for using the form for input into the query. Here is a Simple
Select
(not a crosstab) example

Parameters Forms!frm_CustomReports!StartYear Short;
SELECT a,b,c,d
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)

A crosstab might look like the following and it would if I got all
the
syntax correctly give you a count of c for each year and a grand
total for
the year for each combination of a, b, and d.
Parameters Forms!frm_CustomReports!StartYear Short;
Transform Count(c) as CountEm
SELECT a,b,d,
, Sum(C) as ThisLineTotal
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)
GROUP BY a, b, d
PIVOT 'Year' & (Year(SomeDateField) -
Forms!frm_CustomReports!StartYear) In
('Year0', 'Year1', 'Year2', 'Year3', 'Year4')

"chickalina" wrote in message
...
Disregard Previous....
I meant that I know that you are all helping... I didn't realize
you were
volunteers...
It's late...

3. I put the VBA code in the header and changed it to:

lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0
txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0
etc.
etc.
etc.

I can't figure out what to put here... it's a form where you pick
the year
start from cboYear and click on the Preview Report button... the
above
code
is in the header section... the first label is lbl_Year1. Can I use
a
label?
or should I make it a text box...?

And 4: What do I use for the select statement in the Control Source
for
totaling for the year by Idea in the detail section?
the query is [qry_STRAPforReport Query] and the field names are
"qtrendddate" and "quartervalue". I'm guessing, the SELECT
statement would
pull the year from the same screen where you pick the start date?
so it
would
collect all fields ending in 2007?

And then... the characteristics is another field in tbl_ideas_bank
that
needs to be subtotaled on the bottom.... separate from the year
total. I
guess I mean it would be broken down by the three choices from a
combo box
for 2007 for each country.

I hope this makes better sense.

Thanks again.







  #27  
Old December 7th, 2006, 03:14 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default SELECT Statement in a Report

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5 years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able to
take
an extended period to examine your problem. At this point, I just don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me. You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem. In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:






  #28  
Old December 7th, 2006, 03:18 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default SELECT Statement in a Report

The title is "Dynamic Monthly Crosstab Report ". It describes how to create
a report.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Is there a way to do this in a report?

"Duane Hookom" wrote:

If you are still looking for a method to display the results of a
crosstab
of moving window of years, look at the dynamice monthly crosstab report
solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able
to
easily change from month to year.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:

Is this enough information, or do you need more?
Let me know... Thanks.

"chickalina" wrote:

Here is the table information:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
BenefitType (ETR Only, Cash Only, or ETR &Cash)

tbl_Quarter
IdeaID (dual primary key with Qtrenddate)
QtrEndDate (this is the Quarter end Date)
QuarterValue (These are the values to calculate with)


"John Spencer" wrote:

If you are using a label then you have to set the label's caption.

for 3.
lbl_Year1.Caption=forms![frm_customreports]![ControlNameon
frm_customreports] +0

As for 4, you really need to post the SQL statement. so we have
some
idea of
the column names,etc. I keep thinking that you are trying use a
query
directly as the source for a control. If you are that does not
work.

What columns do you want to Add up horizontally? Across the page
can
probably be handled in the reports source query, or with vba in
the
report's
detail section.


What columns do you want to add up vertically? Depending on what
you
specifically want to do here, this could involve a sub-report or
just
some
calculated controls on the report.

As for using the form for input into the query. Here is a Simple
Select
(not a crosstab) example

Parameters Forms!frm_CustomReports!StartYear Short;
SELECT a,b,c,d
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)

A crosstab might look like the following and it would if I got all
the
syntax correctly give you a count of c for each year and a grand
total for
the year for each combination of a, b, and d.
Parameters Forms!frm_CustomReports!StartYear Short;
Transform Count(c) as CountEm
SELECT a,b,d,
, Sum(C) as ThisLineTotal
FROM SomeTable
WHERE SomeDateField Between
DateSerial(Forms!frm_CustomReports!StartYear,1,1) and
DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31)
GROUP BY a, b, d
PIVOT 'Year' & (Year(SomeDateField) -
Forms!frm_CustomReports!StartYear) In
('Year0', 'Year1', 'Year2', 'Year3', 'Year4')

"chickalina" wrote in
message
...
Disregard Previous....
I meant that I know that you are all helping... I didn't realize
you were
volunteers...
It's late...

3. I put the VBA code in the header and changed it to:

lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0
txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0
etc.
etc.
etc.

I can't figure out what to put here... it's a form where you
pick
the year
start from cboYear and click on the Preview Report button... the
above
code
is in the header section... the first label is lbl_Year1. Can I
use
a
label?
or should I make it a text box...?

And 4: What do I use for the select statement in the Control
Source
for
totaling for the year by Idea in the detail section?
the query is [qry_STRAPforReport Query] and the field names are
"qtrendddate" and "quartervalue". I'm guessing, the SELECT
statement would
pull the year from the same screen where you pick the start
date?
so it
would
collect all fields ending in 2007?

And then... the characteristics is another field in
tbl_ideas_bank
that
needs to be subtotaled on the bottom.... separate from the year
total. I
guess I mean it would be broken down by the three choices from a
combo box
for 2007 for each country.

I hope this makes better sense.

Thanks again.









  #29  
Old December 7th, 2006, 03:21 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

I could... I have had nothing but problems when I tried.

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5 years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able to
take
an extended period to examine your problem. At this point, I just don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me. You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem. In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:







  #30  
Old December 7th, 2006, 03:40 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the
labels at the top of the page work with:
lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0
lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1
lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2
lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3
lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4
The problem is getting the text boxes in the detail section to work.

Here's the code from the report's Record Source:

PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" &
DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS Expr1,
[Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5,
[Year5] AS Expr6 FROM tbl_ReserveAmounts;

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5 years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able to
take
an extended period to examine your problem. At this point, I just don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me. You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem. In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:







 




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 09:11 AM.


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