|
 |
access thread: Re: Run-time error 3071
Message #1 by "Beverley Usher" <bUsher@h...> on Thu, 01 Mar 2001 14:47:01 +0000
|
|
I have a query that is giving me this error, and I can't figure out why
(error message typed below). I often get it if I have entered
inappropriate data for parameters (the query normally gets data from a
form), but in this case, the parameter data is OK. If I run the query
and enter the letter "A", it runs; if I enter "B" or "P" it doesn't. B
and P gather more data than A, but shouldn't be any different otherwise.
The query has 12 parameters (they are input in a form). As long as
neither B nor P are put in one form field, it will run anything we throw
at it. B and P with any combination of other parameters, or alone, in
its field consistently bring up the error.
The only work-around I have come up with is to have a copy of the query
that accepts only the parameter for the particular field. That runs fine
for B and P, but I can't put this in the program and cut off all the
other parameters which are used by other people.
The message reads:
Run-time error "3071":
This 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.
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 01 Mar 2001 07:21:16 -0800
|
|
I've seen this error when Access loses a reference to one of it's needed
libraries. See http://www.mvps.org/access/bugs/bugs0001.htm for details & a
fix.
(That article refers to A97, but I've seen the problem in A2K as well.)
If that's no fix, hit us with the SQL & we'll have a better base of info to
try and answer your question.
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 6:47 AM
To: Access
Subject: [access] Re: Run-time error 3071
I have a query that is giving me this error, and I can't figure out why
(error message typed below). I often get it if I have entered
inappropriate data for parameters (the query normally gets data from a
form), but in this case, the parameter data is OK. If I run the query
and enter the letter "A", it runs; if I enter "B" or "P" it doesn't. B
and P gather more data than A, but shouldn't be any different otherwise.
The query has 12 parameters (they are input in a form). As long as
neither B nor P are put in one form field, it will run anything we throw
at it. B and P with any combination of other parameters, or alone, in
its field consistently bring up the error.
The only work-around I have come up with is to have a copy of the query
that accepts only the parameter for the particular field. That runs fine
for B and P, but I can't put this in the program and cut off all the
other parameters which are used by other people.
The message reads:
Run-time error "3071":
This 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.
Message #3 by "Beverley Usher" <bUsher@h...> on Thu, 01 Mar 2001 16:05:01 +0000
|
|
The SQL follows. I didn't really expect a specific answer to this query,
but was hoping that the error message often signaled something I could
use to track down the problem. Thanks for the reference to the site -
it's great and I will spend lots of time there learning things!
Here it is (I didn't write it, I inherited it)
PARAMETERS Forms![MIS-0086]![Faculty School Code] Text,
Forms![MIS-0086]![Program/Unit Indicator] Text, Forms![MIS-0086]![Course
Code] Text, Forms![MIS-0086]![Course Period] Text,
Forms![MIS-0086]![Course Title] Text, Forms![MIS-0086]![Period Title]
Text, Forms![MIS-0086]!Outcome Text, Forms![MIS-0086]!Funding Text,
Forms![MIS-0086]![Fee Type] Text, Forms![MIS-0086]!FTPT Text,
Forms![MIS-0086]![Student Type] Text, Forms![MIS-0086]!Offer Text;
SELECT DISTINCTROW srstudnt.SRST_SNAME AS Surname, srstudnt.SRST_FNAME
AS Forenames, [SRST_SNAME] & " " & [SRST_FNAME] AS Name,
[MIS-0086].[Student ID], CVDate([SRST_DOB]) AS DOB, [MIS-0086].[Course
Code], [MIS-0086].[Course Period], UCase$([SRPM_FULL_NAME]) AS [Course
Title], UCase$([SRPS_FULL_NAME]) AS [Period Title], [MIS-0086].[Prog
Code], [MIS-0086].[Prog Date], [MIS-0086].[Offer Status],
[MIS-0086].[Offer Date], [MIS-0086].Address, [MIS-0086].Telephone,
srprogrm.SRPM_DEPT AS Dept, srdept.SRDP_DESC_FULL AS [Dept Title],
srprogrm.SRPM_INDICATOR AS [P/U], [MIS-0086].MOA, [MIS-0086].Type,
[MIS-0086].QAStart, [MIS-0086].QAEnd, [MIS-0086].FTPT, [MIS-0086].Out,
[MIS-0086].FeeFund, [MIS-0086].FeeType, [MIS-0086].Expr1
FROM ((([MIS-0086] LEFT JOIN srstudnt ON [MIS-0086].[Student ID]
srstudnt.SRST_ID) LEFT JOIN srprogrm ON [MIS-0086].[Course Code]
srprogrm.SRPM_PROGRAM) LEFT JOIN srdept ON srprogrm.SRPM_DEPT
srdept.SRDP_DEPT_CODE) LEFT JOIN srprgses ON ([MIS-0086].[Course Period]
= srprgses.SRPS_PERIOD) AND ([MIS-0086].[Course Code]
srprgses.SRPS_PROGRAM)
WHERE ((([MIS-0086].[Course Code]) Like
UCase$([Forms]![MIS-0086]![Course Code]) & "*") AND (([MIS-0086].[Course
Period]) Like UCase$([Forms]![MIS-0086]![Course Period]) & "*") AND
((UCase$([SRPM_FULL_NAME])) Like "*" & UCase$([Forms]![MIS-0086]![Course
Title]) & "*") AND ((UCase$([SRPS_FULL_NAME])) Like "*" &
UCase$([Forms]![MIS-0086]![Period Title]) & "*") AND (([MIS-0086].[Offer
Status]) Like UCase$([Forms]![MIS-0086]![Offer]) & "*") AND
((srprogrm.SRPM_DEPT) Like UCase$([Forms]![MIS-0086]![Faculty School
Code]) & "*") AND ((srprogrm.SRPM_INDICATOR) Like
UCase$([Forms]![MIS-0086]![Program/Unit Indicator]) & "*") AND
(([MIS-0086].Type) Like [Forms]![MIS-0086]![Student Type] & "*") AND
(([MIS-0086].FTPT) Like [Forms]![MIS-0086]![FTPT] & "*") AND
(([MIS-0086].Out) Like [Forms]![MIS-0086]![Outcome] & "*") AND
(([MIS-0086].FeeFund) Like [Forms]![MIS-0086]![Funding] & "*") AND
(([MIS-0086].FeeType) Like [Forms]![MIS-0086]![Fee Type] & "*"));
>>> roy.e.pardee@l... 01/03/2001 3:21:16 PM >>>
I've seen this error when Access loses a reference to one of it's
needed
libraries. See http://www.mvps.org/access/bugs/bugs0001.htm for
details & a
fix.
(That article refers to A97, but I've seen the problem in A2K as
well.)
If that's no fix, hit us with the SQL & we'll have a better base of
info to
try and answer your question.
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 6:47 AM
To: Access
Subject: [access] Re: Run-time error 3071
I have a query that is giving me this error, and I can't figure out
why
(error message typed below). I often get it if I have entered
inappropriate data for parameters (the query normally gets data from a
form), but in this case, the parameter data is OK. If I run the query
and enter the letter "A", it runs; if I enter "B" or "P" it doesn't. B
and P gather more data than A, but shouldn't be any different
otherwise.
The query has 12 parameters (they are input in a form). As long as
neither B nor P are put in one form field, it will run anything we
throw
at it. B and P with any combination of other parameters, or alone, in
its field consistently bring up the error.
The only work-around I have come up with is to have a copy of the
query
that accepts only the parameter for the particular field. That runs
fine
for B and P, but I can't put this in the program and cut off all the
other parameters which are used by other people.
The message reads:
Run-time error "3071":
This 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.
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 01 Mar 2001 08:27:59 -0800
|
|
Ay caramba! That's a heck of a query. I hate to say it, but you may indeed
have exceeded Access' ability to evaluate expressions. I count four joins &
a rat's nest of criteria in your WHERE clause.
My advice is to bite the bullet & rework the whole thing. Start with your
FROM clause--take the innermost join & create a new base query based on
those tables. Apply whatever criteria you can. Then create a new query
that joins the base query to another of your tables, use that as a base for
another query, etc. That way you can carve the problem down into chunks
that will be more manageable (for Access' sake & yours--so you have
something you can maintain).
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 8:05 AM
To: Access
Subject: [access] Re: Run-time error 3071
The SQL follows. I didn't really expect a specific answer to this query,
but was hoping that the error message often signaled something I could
use to track down the problem. Thanks for the reference to the site -
it's great and I will spend lots of time there learning things!
Here it is (I didn't write it, I inherited it)
PARAMETERS Forms![MIS-0086]![Faculty School Code] Text,
Forms![MIS-0086]![Program/Unit Indicator] Text, Forms![MIS-0086]![Course
Code] Text, Forms![MIS-0086]![Course Period] Text,
Forms![MIS-0086]![Course Title] Text, Forms![MIS-0086]![Period Title]
Text, Forms![MIS-0086]!Outcome Text, Forms![MIS-0086]!Funding Text,
Forms![MIS-0086]![Fee Type] Text, Forms![MIS-0086]!FTPT Text,
Forms![MIS-0086]![Student Type] Text, Forms![MIS-0086]!Offer Text;
SELECT DISTINCTROW srstudnt.SRST_SNAME AS Surname, srstudnt.SRST_FNAME
AS Forenames, [SRST_SNAME] & " " & [SRST_FNAME] AS Name,
[MIS-0086].[Student ID], CVDate([SRST_DOB]) AS DOB, [MIS-0086].[Course
Code], [MIS-0086].[Course Period], UCase$([SRPM_FULL_NAME]) AS [Course
Title], UCase$([SRPS_FULL_NAME]) AS [Period Title], [MIS-0086].[Prog
Code], [MIS-0086].[Prog Date], [MIS-0086].[Offer Status],
[MIS-0086].[Offer Date], [MIS-0086].Address, [MIS-0086].Telephone,
srprogrm.SRPM_DEPT AS Dept, srdept.SRDP_DESC_FULL AS [Dept Title],
srprogrm.SRPM_INDICATOR AS [P/U], [MIS-0086].MOA, [MIS-0086].Type,
[MIS-0086].QAStart, [MIS-0086].QAEnd, [MIS-0086].FTPT, [MIS-0086].Out,
[MIS-0086].FeeFund, [MIS-0086].FeeType, [MIS-0086].Expr1
FROM ((([MIS-0086] LEFT JOIN srstudnt ON [MIS-0086].[Student ID]
srstudnt.SRST_ID) LEFT JOIN srprogrm ON [MIS-0086].[Course Code]
srprogrm.SRPM_PROGRAM) LEFT JOIN srdept ON srprogrm.SRPM_DEPT
srdept.SRDP_DEPT_CODE) LEFT JOIN srprgses ON ([MIS-0086].[Course Period]
= srprgses.SRPS_PERIOD) AND ([MIS-0086].[Course Code]
srprgses.SRPS_PROGRAM)
WHERE ((([MIS-0086].[Course Code]) Like
UCase$([Forms]![MIS-0086]![Course Code]) & "*") AND (([MIS-0086].[Course
Period]) Like UCase$([Forms]![MIS-0086]![Course Period]) & "*") AND
((UCase$([SRPM_FULL_NAME])) Like "*" & UCase$([Forms]![MIS-0086]![Course
Title]) & "*") AND ((UCase$([SRPS_FULL_NAME])) Like "*" &
UCase$([Forms]![MIS-0086]![Period Title]) & "*") AND (([MIS-0086].[Offer
Status]) Like UCase$([Forms]![MIS-0086]![Offer]) & "*") AND
((srprogrm.SRPM_DEPT) Like UCase$([Forms]![MIS-0086]![Faculty School
Code]) & "*") AND ((srprogrm.SRPM_INDICATOR) Like
UCase$([Forms]![MIS-0086]![Program/Unit Indicator]) & "*") AND
(([MIS-0086].Type) Like [Forms]![MIS-0086]![Student Type] & "*") AND
(([MIS-0086].FTPT) Like [Forms]![MIS-0086]![FTPT] & "*") AND
(([MIS-0086].Out) Like [Forms]![MIS-0086]![Outcome] & "*") AND
(([MIS-0086].FeeFund) Like [Forms]![MIS-0086]![Funding] & "*") AND
(([MIS-0086].FeeType) Like [Forms]![MIS-0086]![Fee Type] & "*"));
>>> roy.e.pardee@l... 01/03/2001 3:21:16 PM >>>
I've seen this error when Access loses a reference to one of it's
needed
libraries. See http://www.mvps.org/access/bugs/bugs0001.htm for
details & a
fix.
(That article refers to A97, but I've seen the problem in A2K as
well.)
If that's no fix, hit us with the SQL & we'll have a better base of
info to
try and answer your question.
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 6:47 AM
To: Access
Subject: [access] Re: Run-time error 3071
I have a query that is giving me this error, and I can't figure out
why
(error message typed below). I often get it if I have entered
inappropriate data for parameters (the query normally gets data from a
form), but in this case, the parameter data is OK. If I run the query
and enter the letter "A", it runs; if I enter "B" or "P" it doesn't. B
and P gather more data than A, but shouldn't be any different
otherwise.
The query has 12 parameters (they are input in a form). As long as
neither B nor P are put in one form field, it will run anything we
throw
at it. B and P with any combination of other parameters, or alone, in
its field consistently bring up the error.
The only work-around I have come up with is to have a copy of the
query
that accepts only the parameter for the particular field. That runs
fine
for B and P, but I can't put this in the program and cut off all the
other parameters which are used by other people.
The message reads:
Run-time error "3071":
This 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.
Message #5 by BPulliam@B... on Thu, 1 Mar 2001 11:06:23 -0600
|
|
I concur with Roy on his analysis.
That query must be is a real hog.
If it isn't now, it will be as soon as Access is confronted with a large
recordset.
That's not to say that it did not take a considerable amount of
thoughtfulness to put it together.
Depending on what resources are available at the desktop that is initiating
this query, I can see this SQL statement as a definite show stopper.
Thanks for contributing the link to the Add-in Roy, and your input has been
very helpful for me.
Best wishes,
-Brian
Murphy's Technology Laws: "Logic is a systematic method of coming to the
wrong conclusion with confidence."
Message #6 by "Beverley Usher" <bUsher@h...> on Thu, 01 Mar 2001 17:13:57 +0000
|
|
Thanks. I'll keep that in mind and run the cropped one once a month till
we replace the whole thing (August 1 go live date on a much needed new
system).
Beverly
>>> roy.e.pardee@l... 01/03/2001 4:27:59 PM >>>
Ay caramba! That's a heck of a query. I hate to say it, but you may
indeed
have exceeded Access' ability to evaluate expressions. I count four
joins &
a rat's nest of criteria in your WHERE clause.
My advice is to bite the bullet & rework the whole thing. Start with
your
FROM clause--take the innermost join & create a new base query based
on
those tables. Apply whatever criteria you can. Then create a new
query
that joins the base query to another of your tables, use that as a
base for
another query, etc. That way you can carve the problem down into
chunks
that will be more manageable (for Access' sake & yours--so you have
something you can maintain).
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 8:05 AM
To: Access
Subject: [access] Re: Run-time error 3071
The SQL follows. I didn't really expect a specific answer to this
query,
but was hoping that the error message often signaled something I could
use to track down the problem. Thanks for the reference to the site -
it's great and I will spend lots of time there learning things!
Here it is (I didn't write it, I inherited it)
PARAMETERS Forms![MIS-0086]![Faculty School Code] Text,
Forms![MIS-0086]![Program/Unit Indicator] Text,
Forms![MIS-0086]![Course
Code] Text, Forms![MIS-0086]![Course Period] Text,
Forms![MIS-0086]![Course Title] Text, Forms![MIS-0086]![Period Title]
Text, Forms![MIS-0086]!Outcome Text, Forms![MIS-0086]!Funding Text,
Forms![MIS-0086]![Fee Type] Text, Forms![MIS-0086]!FTPT Text,
Forms![MIS-0086]![Student Type] Text, Forms![MIS-0086]!Offer Text;
SELECT DISTINCTROW srstudnt.SRST_SNAME AS Surname, srstudnt.SRST_FNAME
AS Forenames, [SRST_SNAME] & " " & [SRST_FNAME] AS Name,
[MIS-0086].[Student ID], CVDate([SRST_DOB]) AS DOB, [MIS-0086].[Course
Code], [MIS-0086].[Course Period], UCase$([SRPM_FULL_NAME]) AS [Course
Title], UCase$([SRPS_FULL_NAME]) AS [Period Title], [MIS-0086].[Prog
Code], [MIS-0086].[Prog Date], [MIS-0086].[Offer Status],
[MIS-0086].[Offer Date], [MIS-0086].Address, [MIS-0086].Telephone,
srprogrm.SRPM_DEPT AS Dept, srdept.SRDP_DESC_FULL AS [Dept Title],
srprogrm.SRPM_INDICATOR AS [P/U], [MIS-0086].MOA, [MIS-0086].Type,
[MIS-0086].QAStart, [MIS-0086].QAEnd, [MIS-0086].FTPT, [MIS-0086].Out,
[MIS-0086].FeeFund, [MIS-0086].FeeType, [MIS-0086].Expr1
FROM ((([MIS-0086] LEFT JOIN srstudnt ON [MIS-0086].[Student ID]
srstudnt.SRST_ID) LEFT JOIN srprogrm ON [MIS-0086].[Course Code]
srprogrm.SRPM_PROGRAM) LEFT JOIN srdept ON srprogrm.SRPM_DEPT
srdept.SRDP_DEPT_CODE) LEFT JOIN srprgses ON ([MIS-0086].[Course
Period]
= srprgses.SRPS_PERIOD) AND ([MIS-0086].[Course Code]
srprgses.SRPS_PROGRAM)
WHERE ((([MIS-0086].[Course Code]) Like
UCase$([Forms]![MIS-0086]![Course Code]) & "*") AND
(([MIS-0086].[Course
Period]) Like UCase$([Forms]![MIS-0086]![Course Period]) & "*") AND
((UCase$([SRPM_FULL_NAME])) Like "*" &
UCase$([Forms]![MIS-0086]![Course
Title]) & "*") AND ((UCase$([SRPS_FULL_NAME])) Like "*" &
UCase$([Forms]![MIS-0086]![Period Title]) & "*") AND
(([MIS-0086].[Offer
Status]) Like UCase$([Forms]![MIS-0086]![Offer]) & "*") AND
((srprogrm.SRPM_DEPT) Like UCase$([Forms]![MIS-0086]![Faculty School
Code]) & "*") AND ((srprogrm.SRPM_INDICATOR) Like
UCase$([Forms]![MIS-0086]![Program/Unit Indicator]) & "*") AND
(([MIS-0086].Type) Like [Forms]![MIS-0086]![Student Type] & "*") AND
(([MIS-0086].FTPT) Like [Forms]![MIS-0086]![FTPT] & "*") AND
(([MIS-0086].Out) Like [Forms]![MIS-0086]![Outcome] & "*") AND
(([MIS-0086].FeeFund) Like [Forms]![MIS-0086]![Funding] & "*") AND
(([MIS-0086].FeeType) Like [Forms]![MIS-0086]![Fee Type] & "*"));
>>> roy.e.pardee@l... 01/03/2001 3:21:16 PM >>>
I've seen this error when Access loses a reference to one of it's
needed
libraries. See http://www.mvps.org/access/bugs/bugs0001.htm for
details & a
fix.
(That article refers to A97, but I've seen the problem in A2K as
well.)
If that's no fix, hit us with the SQL & we'll have a better base of
info to
try and answer your question.
Cheers,
-Roy
-----Original Message-----
From: Beverley Usher [mailto:bUsher@h...]
Sent: Thursday, March 01, 2001 6:47 AM
To: Access
Subject: [access] Re: Run-time error 3071
I have a query that is giving me this error, and I can't figure out
why
(error message typed below). I often get it if I have entered
inappropriate data for parameters (the query normally gets data from a
form), but in this case, the parameter data is OK. If I run the query
and enter the letter "A", it runs; if I enter "B" or "P" it doesn't. B
and P gather more data than A, but shouldn't be any different
otherwise.
The query has 12 parameters (they are input in a form). As long as
neither B nor P are put in one form field, it will run anything we
throw
at it. B and P with any combination of other parameters, or alone, in
its field consistently bring up the error.
The only work-around I have come up with is to have a copy of the
query
that accepts only the parameter for the particular field. That runs
fine
for B and P, but I can't put this in the program and cut off all the
other parameters which are used by other people.
The message reads:
Run-time error "3071":
This 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.
|
|
|
View Cart
