John,
Thanks in advance for the help. Here's what I've got:
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!
John Vinson said:
I just found the answer (I believe ) to one problem about "Jet Engine not
recognizing (X) as a valid field" error message I was getting when trying to
get 2 textbox fields to feed the critirea of a Cross-Tab Query for a date
range, whe I read about the parameters issue.
No, when trying to do as was mentioned in the thread about how to setup
parameters in the Cross-Tab Query, I get another error message about "Invalid
Bracketing". I've tried using just the ! to separate my form name,
(forms!formName!fieldName), I've tried bracketing different methods
(forms![FormName]![FieldName]) or (forms!FormName![FieldName]), etc., but no
matter how I bracket, I still get the error message about invalid bracketing.
Any thoughts!
Please post the SQL view of your query. I suspect there is another
bracket or parenthesis misplaced (perhaps somewhere other than the
parameters).
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
