Graham - thanks for the help (how's the weather 'down-under'?)
The first query is a select query with a date range coming from a form. The
SQL looks like this:
SELECT Comments.CommentID, Comments.CommentDate, Comments.CommentTime,
Comments.StoreLocation, Comments.[MOD], Comments.ServerName,
Comments.QuestionA, Comments.QuestionB, Comments.QuestionC,
Comments.QuestionD, Comments.QuestionE, Comments.QuestionF,
Comments.QuestionG, Comments.QuestionH, Comments.QuestionI,
Comments.QuestionJ, Comments.QuestionK, Comments.QuestionL,
Comments.AdditionalComments, Comments.CustomerFirst, Comments.CustomerLast,
Comments.[CustomerPhone#], Comments.[CustomerFAX#], Comments.CustomerAddress,
Comments.CustomerCity, Comments.CustomerState, Comments.CustomerZip,
Comments.CustomerEmailAddress, *
FROM Comments
WHERE (((Comments.CommentDate)>=[Forms]![ReportsControl]![FromDate] And
(Comments.CommentDate)<=[Forms]![ReportsControl]![ToDate]));
The second query is a Cross-tab which gets its source from the first query.
The SQL looks like this:
PARAMETERS [Forms!ReportsControl!FromDate] DateTime,
[Forms!ReportsControl!ToDate] DateTime;
TRANSFORM Count(CommentsQuery.CommentID) AS CountOfCommentID
SELECT CommentsQuery.StoreLocation, Count(CommentsQuery.CommentID) AS [Total
Of CommentID]
FROM CommentsQuery
GROUP BY CommentsQuery.StoreLocation
PIVOT CommentsQuery.QuestionA;
If I don't put parameters I get an error: "...Jet Engine does not recognize
[Forms]![ReportsControl]![FromDate] as a valid field or expression". When I
do set the parameter (on either query) I get the error: "Invalid Bracketing".
No mater how I change the bracketing I still get the same error message.
I've also used "BETWEEN/AND" instead of the >= and <= expression, but with
the same results.
I'm stumped!
Graham R Seach said:
Post your query.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
tpope999 said:
Duane,
Thanks for the quick response. I tried changingnit in the SQL view but
still the same error message no matter how I change the brackets. Any
more
ideas?
:
Sometimes Access will change your bracketing. Open the query in sql view
and
make sure the bracketing is correct. You might see something like:
[Forms!frmA!txtDate]
--
Duane Hookom
MS Access MVP
After finding the answer (I think) about how to get past the error
"Access
Jet Engine does not recognize [X] as a valid field name or expression"
with a
thread on setting parameters in a cross-tab query, I've now encountered
another error message. When trying to set the parameters in the CTQ, I
get
the error message: "Invalid Bracketing". I've tried bracketing
different
ways, including using only the ! such as: forms!formName!fieldName and
I've
tried bracketing different parts of the [forms]! and [formName]! and
[fieldName] with different combinations of bracket or no-brackets -
still
I
get the error message. Any thoughts?
