Expression typed incorrectly ...

K

Kurt

When I run a crosstab query (SQL below), I sometimes get
this error:

"This expression is typed incorrectly, or is it too
complex to be evaluated."

I can't seem to isolate what's causing this error, but it
seems to depend on how many or which fields I have data
for in the table. For example, if I enter more fields
into an existing record, or add more records, the query
usually works.

A few times, the error occured when I clicked on the
query from the database window, but it didn't occur when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of qrySurveyDate
are contributing to the problem, because if I delete all
three WHERE clauses, the query always works. Likewise, if
I omit qrySurveyDate from the query (with or without the
WHERE statements), the query always works. However, I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
--------------------------------------------------
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");
 
D

Duane Hookom

Most of the time this is due to a criteria in the query from a parameter or
reference to a form control. You must enter your query parameter data types.
Select Query|Parameters and enter your exact parameter and datatype.
 
K

Kurt

Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
Click to expand...

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
------------------------------------------------------
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));
------------------------------------------------------

Here's what I entered in Query | Parameters:
------------------------------------------------------
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
------------------------------------------------------

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt
 
D

Duane Hookom

Are you suggesting the Access only threw up an error when you didn't enter
dates into your form? If so, either always enter dates or change your
criteria to handle nulls.

--
Duane Hookom
Microsoft Access MVP


Kurt said:
Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
Click to expand...

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
------------------------------------------------------
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));
------------------------------------------------------

Here's what I entered in Query | Parameters:
------------------------------------------------------
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
------------------------------------------------------

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt



.
Click to expand...
Click to expand...
 
K

Kurt

Are you suggesting the Access only threw up an error
when you didn't enter dates into your form?
Click to expand...

It threw up the error if I entered dates, and it threw up
the error if I left the dates blank.
If so, either always enter dates or change your
criteria to handle nulls.
Click to expand...

The criteria is set up (incorrectly, I presume) to handle
nulls. It's in the WHERE statement of qrySurveyDate (in
the line between the ***'s):

qrySurveyDate:
---------------------------------------------
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)); ***
---------------------------------------------

However, when I specified the data type in Query |
Parameters like you suggested, I only added two items:
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
Click to expand...
Click to expand...

Should I specifiy two additional items for null values?
If so, what's the appropriate syntax?

Thanks. - Kurt

Duane Hookom
Microsoft Access MVP


Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
Click to expand...

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
------------------------------------------------------
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));
------------------------------------------------------

Here's what I entered in Query | Parameters:
------------------------------------------------------
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
------------------------------------------------------

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt



When I run a crosstab query (SQL below), I sometimes get
this error:

"This expression is typed incorrectly, or is it too
complex to be evaluated."

I can't seem to isolate what's causing this error,
Click to expand...
but
it
seems to depend on how many or which fields I have data
for in the table. For example, if I enter more fields
into an existing record, or add more records, the query
usually works.

A few times, the error occured when I clicked on the
query from the database window, but it didn't occur when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of qrySurveyDate
are contributing to the problem, because if I delete all
three WHERE clauses, the query always works.
Click to expand...
Likewise,
if
I omit qrySurveyDate from the query (with or without the
WHERE statements), the query always works. However, I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
--------------------------------------------------
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");






.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Make the entries mandatory. Don't allow nulls. Place a very early date in
the start and a very late date in the end if the user leaves them null.

--
Duane Hookom
MS Access MVP


Kurt said:
Are you suggesting the Access only threw up an error
when you didn't enter dates into your form?
Click to expand...

It threw up the error if I entered dates, and it threw up
the error if I left the dates blank.
If so, either always enter dates or change your
criteria to handle nulls.
Click to expand...

The criteria is set up (incorrectly, I presume) to handle
nulls. It's in the WHERE statement of qrySurveyDate (in
the line between the ***'s):

qrySurveyDate:
---------------------------------------------
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)); ***
---------------------------------------------

However, when I specified the data type in Query |
Parameters like you suggested, I only added two items:
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
Click to expand...
Click to expand...

Should I specifiy two additional items for null values?
If so, what's the appropriate syntax?

Thanks. - Kurt

Duane Hookom
Microsoft Access MVP


Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
--
Duane Hookom
MS Access MVP

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
------------------------------------------------------
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));
------------------------------------------------------

Here's what I entered in Query | Parameters:
------------------------------------------------------
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
------------------------------------------------------

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt




message
When I run a crosstab query (SQL below), I sometimes
get
this error:

"This expression is typed incorrectly, or is it too
complex to be evaluated."

I can't seem to isolate what's causing this error, but
it
seems to depend on how many or which fields I have data
for in the table. For example, if I enter more fields
into an existing record, or add more records, the query
usually works.

A few times, the error occured when I clicked on the
query from the database window, but it didn't occur
when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of qrySurveyDate
are contributing to the problem, because if I delete
all
three WHERE clauses, the query always works. Likewise,
if
I omit qrySurveyDate from the query (with or without
the
WHERE statements), the query always works. However, I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
--------------------------------------------------
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");






.
Click to expand...


.
Click to expand...
Click to expand...
 
K

Kurt

Make the entries mandatory. Don't allow nulls. Place a
very early date in the start and a very late date in the
end if the user leaves them null.
Click to expand...

I tried this - removing the null permission in
qrySurveyDate and forcing dates - but the error still
occasionally occurs.

And just to clarify, the error *never* occurred in
qrySurveyDate (when run in design view). This query
always worked fine, with or without a null statment
involved, whether dates were entered or not, whether I
had specified the parameter data types in Query |
Parameters.

The error always occured in:

- qxtbQstnTextRspnsxCount (which uses qrySurveyDate), or
- qselQstnTextRspnsxCount (which uses the qxtb query), or
- both of the queries

Again, the error occurred whether dates were entered or
not.

I've mentioned some odd behavior in previous posts:

1. One time, in design view, even though all 3 queries
opened and ran without error, when I tried to open the
report I got the "This expression is typed
incorrectly . . ." error.

2.

a. If I start with no records, add a survey but answer
fewer than ~14 questions: qrySurveyDate runs fine, qxtb
produces the error, qsel Runs fine, the report (which is
based on the qsel report) doesn't open.

b. If I go back to that one survey and enter a 15th
question: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, but the report DOES open.

c. If I go back and delete most of the answers to that
one survey, leaving only the survey date and maybe 1 or 2
answers: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, and the report DOES open (even
though it didn't open when fewer then 14 questions were
answered the first time).

Again, the errors/problems occur whether dates are
entered or not. I've since required a date but the
problem will still occur.

Mystified,

Kurt


Duane Hookom
MS Access MVP


Are you suggesting the Access only threw up an error
when you didn't enter dates into your form?
Click to expand...

It threw up the error if I entered dates, and it threw up
the error if I left the dates blank.
If so, either always enter dates or change your
criteria to handle nulls.
Click to expand...

The criteria is set up (incorrectly, I presume) to handle
nulls. It's in the WHERE statement of qrySurveyDate (in
the line between the ***'s):

qrySurveyDate:
---------------------------------------------
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)); ***
---------------------------------------------

However, when I specified the data type in Query |
Parameters like you suggested, I only added two items:
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate]
Click to expand...
Date/Time

Should I specifiy two additional items for null values?
If so, what's the appropriate syntax?

Thanks. - Kurt

Duane Hookom
Microsoft Access MVP


Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
--
Duane Hookom
MS Access MVP

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
---------------------------------------------------- --
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));
---------------------------------------------------- --

Here's what I entered in Query | Parameters:
---------------------------------------------------- --
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
---------------------------------------------------- --

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt




message
When I run a crosstab query (SQL below), I sometimes
get
this error:

"This expression is typed incorrectly, or is
Click to expand...
it
too
complex to be evaluated."

I can't seem to isolate what's causing this
Click to expand...
error,
but
it
seems to depend on how many or which fields I
Click to expand...
have
data
for in the table. For example, if I enter more fields
into an existing record, or add more records, the query
usually works.

A few times, the error occured when I clicked on the
query from the database window, but it didn't occur
when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of qrySurveyDate
are contributing to the problem, because if I delete
all
three WHERE clauses, the query always works. Likewise,
if
I omit qrySurveyDate from the query (with or without
the
WHERE statements), the query always works.
Click to expand...
However,
I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
------------------------------------------------- -
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of Responses");






.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

If you really want to use this response (date) for criteria etc. you might
consider adding the date field to tblSrvRspns. This would potentially
resolve your issues.

--
Duane Hookom
MS Access MVP


Kurt said:
Make the entries mandatory. Don't allow nulls. Place a
very early date in the start and a very late date in the
end if the user leaves them null.
Click to expand...

I tried this - removing the null permission in
qrySurveyDate and forcing dates - but the error still
occasionally occurs.

And just to clarify, the error *never* occurred in
qrySurveyDate (when run in design view). This query
always worked fine, with or without a null statment
involved, whether dates were entered or not, whether I
had specified the parameter data types in Query |
Parameters.

The error always occured in:

- qxtbQstnTextRspnsxCount (which uses qrySurveyDate), or
- qselQstnTextRspnsxCount (which uses the qxtb query), or
- both of the queries

Again, the error occurred whether dates were entered or
not.

I've mentioned some odd behavior in previous posts:

1. One time, in design view, even though all 3 queries
opened and ran without error, when I tried to open the
report I got the "This expression is typed
incorrectly . . ." error.

2.

a. If I start with no records, add a survey but answer
fewer than ~14 questions: qrySurveyDate runs fine, qxtb
produces the error, qsel Runs fine, the report (which is
based on the qsel report) doesn't open.

b. If I go back to that one survey and enter a 15th
question: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, but the report DOES open.

c. If I go back and delete most of the answers to that
one survey, leaving only the survey date and maybe 1 or 2
answers: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, and the report DOES open (even
though it didn't open when fewer then 14 questions were
answered the first time).

Again, the errors/problems occur whether dates are
entered or not. I've since required a date but the
problem will still occur.

Mystified,

Kurt


Duane Hookom
MS Access MVP


Are you suggesting the Access only threw up an error
when you didn't enter dates into your form?

It threw up the error if I entered dates, and it threw up
the error if I left the dates blank.

If so, either always enter dates or change your
criteria to handle nulls.

The criteria is set up (incorrectly, I presume) to handle
nulls. It's in the WHERE statement of qrySurveyDate (in
the line between the ***'s):

qrySurveyDate:
---------------------------------------------
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)); ***
---------------------------------------------

However, when I specified the data type in Query |
Parameters like you suggested, I only added two items:

[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time

Should I specifiy two additional items for null values?
If so, what's the appropriate syntax?

Thanks. - Kurt


Duane Hookom
Microsoft Access MVP


message
Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
--
Duane Hookom
MS Access MVP

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
---------------------------------------------------- --
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));
---------------------------------------------------- --

Here's what I entered in Query | Parameters:
---------------------------------------------------- --
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate] Date/Time
---------------------------------------------------- --

This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt




message
When I run a crosstab query (SQL below), I sometimes
get
this error:

"This expression is typed incorrectly, or is it
too
complex to be evaluated."

I can't seem to isolate what's causing this error,
but
it
seems to depend on how many or which fields I have
data
for in the table. For example, if I enter more
fields
into an existing record, or add more records, the
query
usually works.

A few times, the error occured when I clicked on the
query from the database window, but it didn't occur
when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of
qrySurveyDate
are contributing to the problem, because if I delete
all
three WHERE clauses, the query always works.
Likewise,
if
I omit qrySurveyDate from the query (with or without
the
WHERE statements), the query always works. However,
I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
------------------------------------------------- -
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of
Responses");






.



.
Click to expand...


.
Click to expand...
Click to expand...
 
K

Kurt

If you really want to use this response (date) for
criteria etc. you might consider adding the date field
Click to expand...
to tblSrvRspns. This would potentially
resolve your issues.
Click to expand...

And that it did. I made this change and everything is
working now. Thanks.
--
Duane Hookom
MS Access MVP



Make the entries mandatory. Don't allow nulls. Place a
very early date in the start and a very late date in the
end if the user leaves them null.
Click to expand...

I tried this - removing the null permission in
qrySurveyDate and forcing dates - but the error still
occasionally occurs.

And just to clarify, the error *never* occurred in
qrySurveyDate (when run in design view). This query
always worked fine, with or without a null statment
involved, whether dates were entered or not, whether I
had specified the parameter data types in Query |
Parameters.

The error always occured in:

- qxtbQstnTextRspnsxCount (which uses qrySurveyDate), or
- qselQstnTextRspnsxCount (which uses the qxtb query), or
- both of the queries

Again, the error occurred whether dates were entered or
not.

I've mentioned some odd behavior in previous posts:

1. One time, in design view, even though all 3 queries
opened and ran without error, when I tried to open the
report I got the "This expression is typed
incorrectly . . ." error.

2.

a. If I start with no records, add a survey but answer
fewer than ~14 questions: qrySurveyDate runs fine, qxtb
produces the error, qsel Runs fine, the report (which is
based on the qsel report) doesn't open.

b. If I go back to that one survey and enter a 15th
question: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, but the report DOES open.

c. If I go back and delete most of the answers to that
one survey, leaving only the survey date and maybe 1 or 2
answers: qrySurveyDate runs fine, qxtb produces the
error, qsel Runs fine, and the report DOES open (even
though it didn't open when fewer then 14 questions were
answered the first time).

Again, the errors/problems occur whether dates are
entered or not. I've since required a date but the
problem will still occur.

Mystified,

Kurt


Duane Hookom
MS Access MVP


Are you suggesting the Access only threw up an error
when you didn't enter dates into your form?

It threw up the error if I entered dates, and it
Click to expand...
threw
up
the error if I left the dates blank.

If so, either always enter dates or change your
criteria to handle nulls.

The criteria is set up (incorrectly, I presume) to handle
nulls. It's in the WHERE statement of qrySurveyDate (in
the line between the ***'s):

qrySurveyDate:
---------------------------------------------
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)); ***
---------------------------------------------

However, when I specified the data type in Query |
Parameters like you suggested, I only added two items:

[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate]
Click to expand...
Date/Time

Should I specifiy two additional items for null values?
If so, what's the appropriate syntax?

Thanks. - Kurt


Duane Hookom
Microsoft Access MVP


message
Most of the time this is due to a criteria in the
query from a parameter or reference to a form
control. You must enter your query parameter data
types. Select Query|Parameters and enter your exact
parameter and datatype.
--
Duane Hookom
MS Access MVP

qrySurveyDate uses a parameter with a reference to a
form, so I added the parameter and type to the Query |
Parameters, but received this error when running the
query:

Invalid use of Null . . .

Data type mismatch in criteria expression

This happened whether I entered paramaters or not when
prompted. Oddly, I've since deleted the Query |
Paramaters values but the error still occurs.

Here's the SQL for qrySurveyDate:
-------------------------------------------------
Click to expand...
---
--
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));
-------------------------------------------------
Click to expand...
---
--
Here's what I entered in Query | Parameters:
-------------------------------------------------
Click to expand...
---
--
[Forms]![frmnuReportSelect]![txtStartDate] Date/Time
[Forms]![frmnuReportSelect]![txtEndDate]
Click to expand...
Date/Time
-------------------------------------------------
Click to expand...
---
--
This is last known bug in this database . . . so close,
so close.

Any ideas? Thank you once again. - Kurt




"Kurt" <[email protected]>
Click to expand...
wrote
in
message
When I run a crosstab query (SQL below), I sometimes
get
this error:

"This expression is typed incorrectly, or
Click to expand...
is
it
too
complex to be evaluated."

I can't seem to isolate what's causing this error,
but
it
seems to depend on how many or which fields I have
data
for in the table. For example, if I enter more
fields
into an existing record, or add more records, the
query
usually works.

A few times, the error occured when I clicked
Click to expand...
on
the
query from the database window, but it didn't occur
when
I ran the query from design view. Weird.

I assume the WHERE clauses and/or use of
qrySurveyDate
are contributing to the problem, because if I delete
all
three WHERE clauses, the query always works.
Likewise,
if
I omit qrySurveyDate from the query (with or without
the
WHERE statements), the query always works. However,
I
need these to produce the data of interest.

Any ideas? - Kurt

SQL
----------------------------------------------
Click to expand...
---
-
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 (((tblResponses.Rspns) Is Not Null) AND
((tblQuestions.RspnsType)<>5) AND
((tblQuestions.QstnType)="Stat"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNum,
tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnText, tblResponses.Rspns
PIVOT "Number of Responses" In ("Number of
Responses");






.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query ODBC Fails - AYS2000 3
Expression typed incorrectly .... wierd query behavior 1
Report text box showing squares 1
Problem with parameter query 11
At Your Survey: Calculate field in cros-tab queries 1
Row source is SQL on SQL (subquery problem) 1
Prevent save if all fields not answered 4
Redisplaying selected in listbox 5

Top