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.
|