By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

access 2003 control source use data from external query

P: 7
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([qryBudgetFilteredByThisFiscalYear12.CNS/Amer12],0),"Currency")
This works fine. However when I change this to:

=Format(nz([qryBudgetFilteredByThisFiscalYear12.CNS/Amer13],0),"Currency")
I get an error saying "Invalid bracketing of name".

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

Any ideas where I should look?
Sep 17 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
12 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,048
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 "qryBudgetFilteredByThisFiscalYear12.CNS/Amer12".

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

let me know if this helps.
Sep 17 '12 #2

P: 7
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.
Sep 17 '12 #3

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

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(tblFundBudget.budget) AS SumOfbudget
  2. SELECT tblFundBudget.lineItem
  3. 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
  4. GROUP BY tblFundBudget.lineItem
  5. 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.
Sep 17 '12 #4

twinnyfo
Expert Mod 2.5K+
P: 3,048
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 qryBudgetFilteredByThisFiscalYear12 query?
Sep 17 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
I'm confused as to why the first one would work. Neither one should work because
[qryBudgetFilteredByThisFiscalYear12.CNS/Amer12]
is wrong as well. It should be
[qryBudgetFilteredByThisFiscalYear12].[CNS/Amer12]
Notice the extra brackets to separate the query alias from the field alias.
Sep 17 '12 #6

P: 7
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.
Sep 17 '12 #7

P: 7
@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 qryBudgetFilteredByThisFiscalYear12.
Sep 17 '12 #8

Rabbit
Expert Mod 10K+
P: 12,315
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.
Sep 17 '12 #9

P: 7
@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.
Sep 17 '12 #10

Rabbit
Expert Mod 10K+
P: 12,315
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.
Sep 17 '12 #11

P: 7
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 qryBudgetFilteredByThisFiscalYear12 as qryBudgetFilteredByThisFiscalYear12.CNS/Amer13. I saw the name qryBudgetFilteredByThisFiscalYear12 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!
Sep 17 '12 #12

Rabbit
Expert Mod 10K+
P: 12,315
Huzzah! Good luck with the rest of your project!
Sep 17 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.