fields in crosstab query (A2000 SR-1)

Topic

New Reply

I have a crosstab query that pulls summarized data from another query. In the first query I had used an expression like Between #03/01/04# and #03/31/04# to select the records that I wanted. The crosstab query worked fine. Then I wanted to get the date parameters from off of a form so it could be easily changed. So I modified my first query’s expression to Between [forms]![frmSalesTaxItems]![SalesTaxBeginDate] And [forms]![frmSalesTaxItems]![SalesTaxEndDate]. These dates are actually stored in a table. The first query runs fine. But the crosstab query won’t run. I tried making a new crosstab query and I get this error message: The Microsoft Jet database engine does not recognize ‘[forms]![frmSalesTaxItems]![SalesTaxBeginDate]’ as a valid field name or expression. Is there a problem using an expression in a query that is the source for a crosstab? Is there a work around?

Reply | Quote

Replies

Did you try putting [forms]![frmSalesTaxItems]![SalesTaxBeginDate] in under Query/Parameters in the crosstab?

Reply | Quote

I don’t believe I can do that–I don’t have any date fields in the cross tab query. The first query is summarizing sales data between two dates by taxed and non-taxed sales in each sales tax jursdiction. So it is supplying the crosstab query with totals for the correct dates.

Reply | Quote

Is the form pre-populated with a series of dates or a single set of 2 dates? Also, would it be possible to just treat the start & end dates as variables and use “Between [Enter start date of reporting period] And [Enter end date of reporting period]”?

Reply | Quote

The form frmSalesTaxItems has two text boxes on it for the beginning and ending dates; their control source is a table. At your suggestion, I tried the Between [Beginning date] And [Ending Date] but I get the same type of error message. If there is no other work around, I’ve thought about having the first query make a temporary table, then basing the crosstab off of a table instead of a query. Then deleting the table.

Reply | Quote

Is the form pre-populated with a series of dates or a single set of 2 dates? Also, would it be possible to just treat the start & end dates as variables and use “Between [Enter start date of reporting period] And [Enter end date of reporting period]”?

Reply | Quote

I don’t believe I can do that–I don’t have any date fields in the cross tab query. The first query is summarizing sales data between two dates by taxed and non-taxed sales in each sales tax jursdiction. So it is supplying the crosstab query with totals for the correct dates.

Reply | Quote

Did you try putting [forms]![frmSalesTaxItems]![SalesTaxBeginDate] in under Query/Parameters in the crosstab?

Reply | Quote

Recommend review this MSKB article & see if it applies:

ACC2000: Error When Running Crosstab Query with a Parameter

Brief excerpt:
[indent]

---

CAUSE
A crosstab query dynamically generates column names. Therefore, Microsoft Access cannot tell whether [XXX] or a form reference is referring to a parameter or to a column name until after the query is bound.

RESOLUTION
To avoid this error, define [XXX] as an explicit parameter by adding it to the Query Parameters dialog box. To do so, follow these steps:

---

[/indent]
See article for full details.

HTH

Reply | Quote

That’s actually what I was going to mention. The parameters I suggested must be entered as parameters in the crosstab by going to Query/Parameters in Design View. Enter the parameters (e.g., [Enter start date]) in the first column and select Date/Time in the second.

Reply | Quote

That’s actually what I was going to mention. The parameters I suggested must be entered as parameters in the crosstab by going to Query/Parameters in Design View. Enter the parameters (e.g., [Enter start date]) in the first column and select Date/Time in the second.

Reply | Quote

I’ve had no luck with the workaround suggested in the knowledge base.

[Feel free to skip this paragraph:] My query is summarizing numbers of patients seen at various sites between specified dates who have certain impediments to learning. Each impediment is a boolean field in the patient’s record in the Patients table. To get all the sites to appear (not just the few who’ve had at least one patient for EVERY impediment in the time range), the queries have to be done in two steps: First, for each impediment, a query counts the number of affected persons at each site in the time range, and throws in a constant field that names the impediment being looked for. Then a union query combines all these results.

Using a PivotTable view in Access 2002, the results of the union query can be viewed as desired (the rows are the sites, the columns are the various impediments, and the values are the counts of affected patients)

But the crosstab query can’t be formulated in either Access 2002 or Access 2000. I’ve tried making the start date and end date into typed parameters in the underlying queries (where they actually ARE parameters), but then I get the error:

Invalid bracketing of name . (Error 3126)
The specified name either cannot have brackets around it or the brackets are mismatched. Check your entry to make sure the brackets are properly matched, and then try the operation again.

The brackets ARE correct, though, and the ones around the terms that don’t have spaces in them are added by Access itself, i.e., in
“>=[forms]![Get Dates Dialog]![txtStartDate]”, the brackets around “forms” and “txtStartDate” are added by Access.

The fact the the PivotTable view works just fine, and the Crosstab doesn’t, indicates to me that the Crosstab machinery is just defective.

Jim Beard

Reply | Quote

Specifying the parameters explicitly should work, but it’s hard to say what causes your problems without seeing the database. Perhaos you could post a stripped-down copy:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you’re using Access 2000 or later.)
• Attach the zip file to a reply.
  [/list]

Reply | Quote

It’s not so easy to get a 12-MB, 2-file data base down to a 100K zip file! Anyway, I think the attached database still functions and illustrates the problem.

To demonstrate, open form “Get Dates Dialog”, enter starting and ending dates (June, 2002 or later) and choose OK to execute the code and hide the form. Then try to get the results of the query, “Limitations (all) by date and site” into a crosstab form without first copying them into a temporary table.

Jim

Reply | Quote

After specifying the parameters in the queries contributing to the union query, I was able to create a crosstab query based on the union query. See attached version.

Reply | Quote

Hans,

Your crosstab query in the example version works perfectly, as you know. However, when I take the specimen that I posted and attempt to make declared parameters of Forms![Get Dates Dialog]!txtStartDate and the end date, I get the bracketing error I described in my earlier post. I’m using Access 2002 SP3. In fact, if I take YOUR copy of the parameterized query, edit | cut one of the parameters, close the parameters box, open it again, paste the text back in and give it a date/time type, I then get the error! (All this is done in “Design View”.)

By examining the query in SQL format before and after re-entering the parameter info, I finally found the error that my copy of Access makes. It puts brackets around the entire parameter expression, as follows: “[[forms]![Get Dates Dialog]![txtStartDate]]” Notice the double opening brackets. But then it can’t parse the resulting expression, and the version it quotes in the error message does indeed have a bracketing mismatch. (I hadn’t previously noticed the that the version that the error message quotes me does have a bracket-count error, though the expression in the parameter window does not.) Your version, which works fine, does not have that extra set of surrounding brackets in its SQL. If I edit them out of the SQL that my copy of Access creates, I can get my query to work.

I wonder if it’s worth trying to explain the bug to Microsoft.

Jim

Reply | Quote

Consider it a “feature”. In versions of Access prior to 2000, the query engine would accept parameters without the brackets or with brackets around only some parts of the form reference. The query engine changed quite a bit with Access 2000, and those parameters that had partial brackets got reinterpreted as something besides a valid form reference. One of the more annoying things about 2000 is that you have to manually fix those parameters or you run into the problem you encountered. If you fix the bracketing and resave the query, it should work properly thereafter. Note that it only occurs if you had *some* brackets in the parameter. If you had none, the conversion takes care of it, but if you had brackets around the name of the form but not the other parts of the reference, you wind up with the situation you have here.

Reply | Quote

Now THAT’s my idea of an interesting “feature” (bug). Especially the last part, where putting only the necessary brackets causes failure.

Reply | Quote

Now THAT’s my idea of an interesting “feature” (bug). Especially the last part, where putting only the necessary brackets causes failure.

Reply | Quote

Consider it a “feature”. In versions of Access prior to 2000, the query engine would accept parameters without the brackets or with brackets around only some parts of the form reference. The query engine changed quite a bit with Access 2000, and those parameters that had partial brackets got reinterpreted as something besides a valid form reference. One of the more annoying things about 2000 is that you have to manually fix those parameters or you run into the problem you encountered. If you fix the bracketing and resave the query, it should work properly thereafter. Note that it only occurs if you had *some* brackets in the parameter. If you had none, the conversion takes care of it, but if you had brackets around the name of the form but not the other parts of the reference, you wind up with the situation you have here.

Reply | Quote

Hans,

Your crosstab query in the example version works perfectly, as you know. However, when I take the specimen that I posted and attempt to make declared parameters of Forms![Get Dates Dialog]!txtStartDate and the end date, I get the bracketing error I described in my earlier post. I’m using Access 2002 SP3. In fact, if I take YOUR copy of the parameterized query, edit | cut one of the parameters, close the parameters box, open it again, paste the text back in and give it a date/time type, I then get the error! (All this is done in “Design View”.)

By examining the query in SQL format before and after re-entering the parameter info, I finally found the error that my copy of Access makes. It puts brackets around the entire parameter expression, as follows: “[[forms]![Get Dates Dialog]![txtStartDate]]” Notice the double opening brackets. But then it can’t parse the resulting expression, and the version it quotes in the error message does indeed have a bracketing mismatch. (I hadn’t previously noticed the that the version that the error message quotes me does have a bracket-count error, though the expression in the parameter window does not.) Your version, which works fine, does not have that extra set of surrounding brackets in its SQL. If I edit them out of the SQL that my copy of Access creates, I can get my query to work.

I wonder if it’s worth trying to explain the bug to Microsoft.

Jim

Reply | Quote

After specifying the parameters in the queries contributing to the union query, I was able to create a crosstab query based on the union query. See attached version.

Reply | Quote

It’s not so easy to get a 12-MB, 2-file data base down to a 100K zip file! Anyway, I think the attached database still functions and illustrates the problem.

To demonstrate, open form “Get Dates Dialog”, enter starting and ending dates (June, 2002 or later) and choose OK to execute the code and hide the form. Then try to get the results of the query, “Limitations (all) by date and site” into a crosstab form without first copying them into a temporary table.

Jim

Reply | Quote

Specifying the parameters explicitly should work, but it’s hard to say what causes your problems without seeing the database. Perhaos you could post a stripped-down copy:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it. (Of course, this only helps if you’re using Access 2000 or later.)
• Attach the zip file to a reply.
  [/list]

Reply | Quote

I’ve had no luck with the workaround suggested in the knowledge base.

[Feel free to skip this paragraph:] My query is summarizing numbers of patients seen at various sites between specified dates who have certain impediments to learning. Each impediment is a boolean field in the patient’s record in the Patients table. To get all the sites to appear (not just the few who’ve had at least one patient for EVERY impediment in the time range), the queries have to be done in two steps: First, for each impediment, a query counts the number of affected persons at each site in the time range, and throws in a constant field that names the impediment being looked for. Then a union query combines all these results.

Using a PivotTable view in Access 2002, the results of the union query can be viewed as desired (the rows are the sites, the columns are the various impediments, and the values are the counts of affected patients)

But the crosstab query can’t be formulated in either Access 2002 or Access 2000. I’ve tried making the start date and end date into typed parameters in the underlying queries (where they actually ARE parameters), but then I get the error:

Invalid bracketing of name . (Error 3126)
The specified name either cannot have brackets around it or the brackets are mismatched. Check your entry to make sure the brackets are properly matched, and then try the operation again.

The brackets ARE correct, though, and the ones around the terms that don’t have spaces in them are added by Access itself, i.e., in
“>=[forms]![Get Dates Dialog]![txtStartDate]”, the brackets around “forms” and “txtStartDate” are added by Access.

The fact the the PivotTable view works just fine, and the Crosstab doesn’t, indicates to me that the Crosstab machinery is just defective.

Jim Beard

Reply | Quote

Recommend review this MSKB article & see if it applies:

ACC2000: Error When Running Crosstab Query with a Parameter

Brief excerpt:
[indent]

---

CAUSE
A crosstab query dynamically generates column names. Therefore, Microsoft Access cannot tell whether [XXX] or a form reference is referring to a parameter or to a column name until after the query is bound.

RESOLUTION
To avoid this error, define [XXX] as an explicit parameter by adding it to the Query Parameters dialog box. To do so, follow these steps:

---

[/indent]
See article for full details.

HTH

Reply | Quote