K
Kurt
Odd query behavior . . . Here's how I came to discover
this problem:
The database has no records, and I enter a new survey.
A. If I enter responses for 14 questions or less (doesn't
seem to matter which ones, but the Survey Date is always
entered), and skip the remaining 8 questions:
- a report will *not* open up
- the query the report is based on
(qSELQstnTextRspnsxCount) produces this error:
"The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric
expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of
the expression to variables."
- a query which is joined to the above qSEL... query runs
fine (without errors)
B. If I go back to the data entry form and answer at
least one more question (so at least 15 questions are
answered):
- the report *will* open up
- the query the report is based on
(qSELQstnTextRspnsxCount) *still* produces the error
- the query which is joined to the qSEL... query runs
fine without errors
** If the query can't run in database window or design
view, how is the report still opening up and showing the
correct data? **
C. If I then go back to the form and delete several of my
responses, even ending up with only 1 or 2 questions
answered:
- the report opens up (even though it didn't open up the
first time when fewer than 14 questions were answered)
- the underlying queries do the same thing (the query for
the report produces the error, the other query runs fine)
-----
Other oddities:
1. Sometimes the report query (qSELQstnTextRspnsxCount)
doesn't produce the error, though I can't figure out why.
2. Sometimes the error will occur if I click on the query
in database window, but it won't occur if I run the query
in design view
-----
Any ideas on what's going on? (SQL below) - Kurt
qselQstnTextRspnsxCount (query for report)
------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns, qxtbQstnTextRspnsxCount.
[Number of Responses], tblClinicInformation.clinic,
tblClinicInformation.address1,
tblClinicInformation.address2,
tblClinicInformation.city, tblClinicInformation.state,
tblClinicInformation.zip, tblClinicInformation.phone,
tblClinicInformation.fax
FROM qxtbQstnTextRspnsxCount, tblClinicInformation
WHERE (((qxtbQstnTextRspnsxCount.Rspns) Is Not Null));
qxtbQstnTextRspnsxCount (used in above query)
---------------------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
FROM tblQuestions INNER JOIN (qrySurveyDate INNER JOIN
tblResponses ON qrySurveyDate.RspnsID =
tblResponses.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo") AND
((tblQuestions.RspnsType)<>5))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
qrySurveyDate (used in above query)
-----------------------------------
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [Forms]!
[frmnuReportSelect]![txtStartDate] And [Forms]!
[frmnuReportSelect]![txtEndDate]) AND
((tblResponses.QstnID)=2)) OR (((tblResponses.QstnID)
=2) AND (((CDate([Rspns])) Like ((CDate([Rspns]))
Between [Forms]![frmnuReportSelect]![txtStartDate]
And [Forms]![frmnuReportSelect]![txtEndDate])) Is
Null));
this problem:
The database has no records, and I enter a new survey.
A. If I enter responses for 14 questions or less (doesn't
seem to matter which ones, but the Survey Date is always
entered), and skip the remaining 8 questions:
- a report will *not* open up
- the query the report is based on
(qSELQstnTextRspnsxCount) produces this error:
"The expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric
expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of
the expression to variables."
- a query which is joined to the above qSEL... query runs
fine (without errors)
B. If I go back to the data entry form and answer at
least one more question (so at least 15 questions are
answered):
- the report *will* open up
- the query the report is based on
(qSELQstnTextRspnsxCount) *still* produces the error
- the query which is joined to the qSEL... query runs
fine without errors
** If the query can't run in database window or design
view, how is the report still opening up and showing the
correct data? **
C. If I then go back to the form and delete several of my
responses, even ending up with only 1 or 2 questions
answered:
- the report opens up (even though it didn't open up the
first time when fewer than 14 questions were answered)
- the underlying queries do the same thing (the query for
the report produces the error, the other query runs fine)
-----
Other oddities:
1. Sometimes the report query (qSELQstnTextRspnsxCount)
doesn't produce the error, though I can't figure out why.
2. Sometimes the error will occur if I click on the query
in database window, but it won't occur if I run the query
in design view
-----
Any ideas on what's going on? (SQL below) - Kurt
qselQstnTextRspnsxCount (query for report)
------------------------------------------
SELECT DISTINCTROW qxtbQstnTextRspnsxCount.QstnID,
qxtbQstnTextRspnsxCount.QstnNum,
qxtbQstnTextRspnsxCount.QstnLvl1,
qxtbQstnTextRspnsxCount.QstnLvl2,
qxtbQstnTextRspnsxCount.QstnText,
qxtbQstnTextRspnsxCount.Rspns, qxtbQstnTextRspnsxCount.
[Number of Responses], tblClinicInformation.clinic,
tblClinicInformation.address1,
tblClinicInformation.address2,
tblClinicInformation.city, tblClinicInformation.state,
tblClinicInformation.zip, tblClinicInformation.phone,
tblClinicInformation.fax
FROM qxtbQstnTextRspnsxCount, tblClinicInformation
WHERE (((qxtbQstnTextRspnsxCount.Rspns) Is Not Null));
qxtbQstnTextRspnsxCount (used in above query)
---------------------------------------------
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
FROM tblQuestions INNER JOIN (qrySurveyDate INNER JOIN
tblResponses ON qrySurveyDate.RspnsID =
tblResponses.RspnsID) ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.QstnType)="Stat" Or
(tblQuestions.QstnType)="Demo") AND
((tblQuestions.RspnsType)<>5))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
qrySurveyDate (used in above query)
-----------------------------------
SELECT tblResponses.RspnsID
FROM tblResponses
WHERE (((CDate([Rspns])) Between [Forms]!
[frmnuReportSelect]![txtStartDate] And [Forms]!
[frmnuReportSelect]![txtEndDate]) AND
((tblResponses.QstnID)=2)) OR (((tblResponses.QstnID)
=2) AND (((CDate([Rspns])) Like ((CDate([Rspns]))
Between [Forms]![frmnuReportSelect]![txtStartDate]
And [Forms]![frmnuReportSelect]![txtEndDate])) Is
Null));