Replies

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

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

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

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

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

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

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

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

Thanks, Jefferson. I’m chagrined that I spent most of an hour searching through the vbscript and WSH documentation trying to find such a thing (shellObject.BrowseForFolder), and Andrew’s code clearly indicates that it exists.

Thanks, Jefferson. I’m chagrined that I spent most of an hour searching through the vbscript and WSH documentation trying to find such a thing (shellObject.BrowseForFolder), and Andrew’s code clearly indicates that it exists.

Alas, I’m forever installing new software or removing old, so I can’t say what was happening around the time this showed up. I do run antivirus and firewall software, and periodically run Ad-Aware and SpyBot. Haven’t had particular problems there. I have spent many puzzled minutes gazing at the activity on Task manager, and am none the wiser for it.

Alas, I’m forever installing new software or removing old, so I can’t say what was happening around the time this showed up. I do run antivirus and firewall software, and periodically run Ad-Aware and SpyBot. Haven’t had particular problems there. I have spent many puzzled minutes gazing at the activity on Task manager, and am none the wiser for it.

I never was able to find the posting referred to. (Searching on the API call name was fruitless, and unfortunately I don’t know who Andrew is.)

I did find it possible to use drag-and-drop for the purpose. That is, I can use Explorer to locate the file I want to perform the script actions on, and drag and drop it onto the VBscript file’s icon. I’d still be very interested to know how some API’s might be called.

I never was able to find the posting referred to. (Searching on the API call name was fruitless, and unfortunately I don’t know who Andrew is.)

I did find it possible to use drag-and-drop for the purpose. That is, I can use Explorer to locate the file I want to perform the script actions on, and drag and drop it onto the VBscript file’s icon. I’d still be very interested to know how some API’s might be called.

Thanks. I was imprecise about the problems. First, I couldn’t recall how to make the F1 key work right; this was fixed by your suggestion. Second, I didn’t know how to make the “Contents” and “Index” pages appear. These I found with the little “Show tabs” icon in the upper part of the Help window. So I’m much happier. The last thing was regarding context-sensitive help, but now I’m not at all sure that Word ever offered this, except in the Visual Basic editor.