access 2003 control source use data from external query

[CarlosThomas]

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")
I get an error saying "Invalid bracketing of name".

The query qryBudgetFilter edByThisFiscalY ear12 is a crosstab. One of the column headings is CNS/Amer12 and one is CNS/Amer13.

Any ideas where I should look?

[twinnyfo]

Carlos,

The first place I would look is to remove the slash from the query field name. since the slash can also be used for division, it is never wise to include that in a field name. This may be causing your second example above to try to divide two values.

I can't explain why it works for the first one, unles you have a control on the report that has the name "qryBudgetFilte redByThisFiscal Year12.CNS/Amer12".

Just throwing out some ideas and additional DB best practices advice.

let me know if this helps.

[CarlosThomas]

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 second one produced the same error message.

[CarlosThomas]

This is the SQL from the query I am trying to use as the control source:

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","DOL 1","DOL10","DOE/YB","REB/YB","OWD","WAL","Unrestricted","CHA","REB","SOM","Hyams","CommCorp","OJJDP","SNAP","HUD02","DYS","CNS/Amer12","PostSec","UW");

I just found out that AmRec doesn't work either.

[twinnyfo]

VERY strange........

However, I can also foresee you creating queries for every Fiscal Year... I also hesitate using Cross Tab queries unless I am absolutely, positively certain that all of the potential fields are guaranteed to be in the results of the cross tab every single time. For example, if there were no values for CNS/Amer13, you would not have that field name available to other objects in your database.

According to theory, if you have data in both columns, both control sources should work on your report.

What are the typical results of your qryBudgetFilter edByThisFiscalY ear12 query?

[Rabbit]

I'm confused as to why the first one would work. Neither one should work because
[qryBudgetFilter edByThisFiscalY ear12.CNS/Amer12]
is wrong as well. It should be
[qryBudgetFilter edByThisFiscalY ear12].[CNS/Amer12]
Notice the extra brackets to separate the query alias from the field alias.

[CarlosThomas]

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.

[CarlosThomas]

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

[Rabbit]

That is most likely because the alias is incorrect. Either because you gave it a different alias, or it's because of a typo, or because the query is not actually in the record source.

If you try to reference a cross tab column for which the data does not exist in the base records, then you will get an error.

[CarlosThomas]

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

[Rabbit]

I'm not sure how you're even able to pull the value without a DLookup. If it's not a part of the recordsource, you can't reference a query value in that way. If you try to, you end up getting a #Name? error. So there's something else going on here that we're unaware of.

[CarlosThomas]

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 source query and saw that CNS/Amer13 was missing while all the other funders were there. I added CNS/Amer13 and it now works!

Thank you both!

[Rabbit]

Huzzah! Good luck with the rest of your project!