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 |
#30
|
|||
|
|||
SELECT Statement in a Report
My suggestion was to use text boxes rather than labels. Why write code if
you don't have to? It looks like this report depends on both "frm_CustomReports" and "frm_CustomSearch". Why don't you have all criteria on a single form? Do you actually maintain a table that has YearX as field names? -- Duane Hookom MS Access MVP "chickalina" wrote in message ... 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 | |
|
|