CarlosThomas
Last Activity: Sep 17 '12, 06:24 PM
Joined: Sep 17 '12
Location:
-
I figured it out with your help. The reference in the control source is to a field name in the query of the record source. The record source query is a union query that pulls CNS/Amer13 from qryBudgetFilter edByThisFiscalY ear12 as qryBudgetFilter edByThisFiscalY ear12.CNS/Amer13. I saw the name qryBudgetFilter edByThisFiscalY ear12 and couldn't figure out why it wouldn't work, since CNS/Amer13 was clearly a field in this query. I finally checked the record... -
@Rabbit - The query I am trying to reference is different from the record source of the report. However, this does work as has worked quite well for a number of years. I just don't understand why it's not working for this specific column heading.Leave a comment:
-
@Rabbit - Thanks for your response. I have tried that syntax as well. When I do it that way, I am prompted to enter a value for qryBudgetFilter edByThisFiscalY ear12.Leave a comment:
-
The query organizes spending by line item and funder. We do create a new query for each fiscal year.
When I run the query there are some columns with no entries. CNS/Amer12 is one of these, so is AmRec. As I mentioned, CNS/Amer12 works fine on the report, but AmRec seems not to. CNS/Amer13 has plenty of entries.Leave a comment:
-
This is the SQL from the query I am trying to use as the control source:
...Code:TRANSFORM Sum(tblFundBudget.budget) AS SumOfbudget SELECT tblFundBudget.lineItem FROM z_fund INNER JOIN ((z_fyFund INNER JOIN this ON z_fyFund.fyID = this.fiscalYear) INNER JOIN tblFundBudget ON z_fyFund.fyfundID = tblFundBudget.fyfundID) ON z_fund.fundID = z_fyFund.fundID GROUP BY tblFundBudget.lineItem PIVOT z_fund.fundName In ("CNS/Amer11","CNS/Amer10","CNS/Amer13","AmRec","CDBG","HUD06","DOLLeave a comment:
-
Thanks for the help. I was looking at the slash thinking it shouldn't be there. Unfortunately, I have so many nested queries that it would take me a couple of hours to remove so I am trying to avoid this.
I did try to create a new blank report with the same record source as the report in question. I then added a text control and used both of the expressions entered in my question and received similar results: the first one work, the...Leave a comment:
-
access 2003 control source use data from external query
I have a complex report in Access 2003. In addition to the query that feeds the report, I have some control sources set to gather data from other queries.
One field uses the following for the control source:
=Format(nz([qryBudgetFilter edByThisFiscalY ear12.CNS/Amer12],0),"Currency")
This works fine. However when I change this to:
=Format(nz([qryBudgetFilter edByThisFiscalY ear12.CNS/Amer13],0),"Currency")...
No activity results to display
Leave a comment: