he expression is typed incorrectly or it is too complex to be evaluated

[Guest]

I am trying to run the following agregate function in a parameterized query
on Access2000:

Min([t1].[f1]*sqr([t2].[f1]/[t1].[f1])/[param])

The query saved OK, but an attempt to run it results in the message:
The expression is typed incorrectly or it is too complex to be evaluated

If the sintax correct? Perhaps it is, otherwise it would not save.
What can be done about it?

 

[Bob Barrows]

I am trying to run the following agregate function in a parameterized
query on Access2000:

Min([t1].[f1]*sqr([t2].[f1]/[t1].[f1])/[param])

The query saved OK, but an attempt to run it results in the message:
The expression is typed incorrectly or it is too complex to be
evaluated

If the sintax correct? Perhaps it is, otherwise it would not save.
What can be done about it?

Do you get this message when running it in Access, or when running it from
ASP?

I suppose you've confirmed that this expression (which appears to be
syntactically correct) is the culprit by running the query after removing
this expression?

I suggest that you discover what part of the expression is the problem by
starting with:

Min([t1].[f1])

and adding on pieces until it fails.


FWIW, I just tested it in my sample database, and the query runs without
problem.

Bob Barrows

 

[Guest]

Thanks, Bob.

1. The mentioned error pops up when I run this query from within Access2000

When I run it from ASP, it returns
===============================
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
================================

2. The query was working fine both in Access and in ASP before I added that
sqr():
Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param]
So sqr() seems to be a culprit.

Are you saying that on you machine the query with sqr() worked?

I am trying to run the following agregate function in a parameterized
query on Access2000:

Min([t1].[f1]*sqr([t2].[f1]/[t1].[f1])/[param])

The query saved OK, but an attempt to run it results in the message:
The expression is typed incorrectly or it is too complex to be
evaluated

If the sintax correct? Perhaps it is, otherwise it would not save.
What can be done about it?

Do you get this message when running it in Access, or when running it from
ASP?

I suppose you've confirmed that this expression (which appears to be
syntactically correct) is the culprit by running the query after removing
this expression?

I suggest that you discover what part of the expression is the problem by
starting with:

Min([t1].[f1])

and adding on pieces until it fails.


FWIW, I just tested it in my sample database, and the query runs without
problem.

Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows]

Thanks, Bob.

1. The mentioned error pops up when I run this query from within
Access2000

When I run it from ASP, it returns
===============================
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
================================

2. The query was working fine both in Access and in ASP before I
added that sqr():
Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param]
So sqr() seems to be a culprit.

Are you saying that on you machine the query with sqr() worked?

Yes, I had no problem running it.in AccessXP.

A quick Google search shows that somebody solved this problem by declaring
his parameters in a PARAMETERS section of the query, like this:
PARAMETERS param Short;
SELECT ...

Bob Barrows

 

[Guest]

Thanks, Bob.
1. How exactly do I fit
PARAMETERS param Short;
SELECT ...
into my query?

I did it like that

PARAMETERS param Short;
SELECT Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param] AS Expr1
FROM t1,t2
WHERE something;

It seem to work in Access, but in ASP it still coases the same error
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

Also stange that in Access, although it works, it produces result of
calculations different then without PARAMETERS param Short;
Different to the extent that is does not produce nonsense, but still well
above rounding inaccuracy, like 40 and 45

Thanks, Bob.

1. The mentioned error pops up when I run this query from within
Access2000

When I run it from ASP, it returns
===============================
Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
================================

2. The query was working fine both in Access and in ASP before I
added that sqr():
Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param]
So sqr() seems to be a culprit.

Are you saying that on you machine the query with sqr() worked?

Yes, I had no problem running it.in AccessXP.

A quick Google search shows that somebody solved this problem by declaring
his parameters in a PARAMETERS section of the query, like this:
PARAMETERS param Short;
SELECT ...

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows]

Thanks, Bob.
1. How exactly do I fit
PARAMETERS param Short;
SELECT ...
into my query?

Exactly as you did it below

I did it like that

PARAMETERS param Short;
SELECT Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param] AS Expr1
FROM t1,t2
WHERE something;

It seem to work in Access, but in ASP it still coases the same error
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

How are you running the query?

Also stange that in Access, although it works, it produces result of
calculations different then without PARAMETERS param Short;
Different to the extent that is does not produce nonsense, but still
well above rounding inaccuracy, like 40 and 45

I took a guess as to what type of value you would be passing in the
parameter. "Short" is "short integer". You should use the appropriate
datatype for the parameter you are attempting to pass. The Access query
builder has a dialog box to help you create these parameters. From the menu:
Query|Parameters

Bob Barrows

 

[Guest]

Thnanks, Bob
I run the query in ASP using:

objCom.CommandText=QueryName & "'" & param1 & "'," & "'" & param2 &"'"
set objRS(3)=objCom.Execute

It is param2 which is passed to the query as param.

Please note that this has been working fine before I added the sqr()

Thanks, Bob.
1. How exactly do I fit
PARAMETERS param Short;
SELECT ...
into my query?

Exactly as you did it below

I did it like that

PARAMETERS param Short;
SELECT Min([t1].[f1]*[t2].[f1]/[t1].[f1])/[param] AS Expr1
FROM t1,t2
WHERE something;

It seem to work in Access, but in ASP it still coases the same error
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

How are you running the query?

Also stange that in Access, although it works, it produces result of
calculations different then without PARAMETERS param Short;
Different to the extent that is does not produce nonsense, but still
well above rounding inaccuracy, like 40 and 45

I took a guess as to what type of value you would be passing in the
parameter. "Short" is "short integer". You should use the appropriate
datatype for the parameter you are attempting to pass. The Access query
builder has a dialog box to help you create these parameters. From the menu:
Query|Parameters

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows]

Thnanks, Bob
I run the query in ASP using:

objCom.CommandText=QueryName & "'" & param1 & "'," & "'" & param2 &"'"

Your passing both of your parameters as strings - isn't one of them supposed
to be a number? Do not delimit numeric parameters.

set objRS(3)=objCom.Execute

Why do you have "objRS(3)"?
Do you have an array containing recordset objects?

It is param2 which is passed to the query as param.

Please note that this has been working fine before I added the sqr()

Instead of using the Command object, do this:

Set objRS = server.createobject("adodb.recordset")
objConn.QueryName param1,param2,objRS

or, if you really do have an array containing your recordset objects:

Set objRS(3) = server.createobject("adodb.recordset")
objConn.QueryName param1,param2,objRS(3)


If you still get the type mismatch, explicitly cast the parameter variables
to the proper datatypes (CInt and CDbl are for the sake of example - I don't
know what datatypes are needed in your query):

Set objRS = server.createobject("adodb.recordset")
objConn.QueryName CInt(param1),CDbl(param2),objRS

Of course, you still need to make sure the parameters in your query have the
proper datatypes in your PARAMETERS statement.

Another thing to be aware of is that the sqr() may be resulting in a new
datatype which will not be correct in the final result. I doubt that this is
the case, because this situation usually leads to an "arithmetic overflow"
error rather than a type mismatch.

Bob Barrows

 

[aa]

Thanks, Bob

1. isn't one of them supposed to be a number? Do not delimit numeric
parameters.

Yes, you are right, it is a number and I do not need comas for them.
Actually when doing this code I tried it without commas and it failed.
So far it worked well with commas - perhaps there is a conversion somewhere
within Jet?
Anywhay, I removed comas but the error message is the same.

2. Why do you have "objRS(3)"? Do you have an array containing recordset
objects?
Yes.

3. Instead of using the Command object, do this:

Set objRS = server.createobject("adodb.recordset")
objConn.QueryName param1,param2,objRS

I did, but it took time before I realised that QueryName is not a method of
Connection object, but a literal, a string which is the name of my query.
What a strange syntax.

Anywhay, it resulted in the same error.
Why did you suggested trying a different method? Is it more robust than
objCom.Execute ?

 

[Guest]

Bob,

It looks like adding sqr() changed the order in which the parameters should
be passed to the query from ASP.

How do you fetermine in which order the parameters hould be listed in the
query like
objCom.CommandText=QueryName & "'" & param1 & "'," & "'" & param2 &"'"
if there are more then one parameter in the stored query?
It does not seem to foloow the order in which the parameters appear in the
query.

 

[Bob Barrows]

Thanks, Bob

1. isn't one of them supposed to be a number? Do not delimit numeric
parameters.

Yes, you are right, it is a number and I do not need comas for them.
Actually when doing this code I tried it without commas and it failed.
So far it worked well with commas - perhaps there is a conversion
somewhere within Jet?
Anywhay, I removed comas but the error message is the same.

2. Why do you have "objRS(3)"? Do you have an array containing
recordset objects?
Yes.

3. Instead of using the Command object, do this:

I did, but it took time before I realised that QueryName is not a
method of Connection object, but a literal, a string which is the
name of my query. What a strange syntax.

Actually, it's a very neat way of executing a saved query: you execute it as
if it was a native method of the Connection object. (don't try it in .Net -
it won't work)

Anywhay, it resulted in the same error.
Why did you suggested trying a different method? Is it more robust
than objCom.Execute ?

Yes, it really helps avoid datatype mistakes caused by using the wrong
delimiters in the dynamic sql statement. Actually, if you set the
CommandType to adCmdStoredProc, and use the Parameters collection of the
command object, instead of concatenating a dynamic sql statement to run your
saved query, the Command object is just as robust. It's the dynamic sql
business that I dislike (others like it).

I think it's time you showed me your table structure - field names and
datatypes (the fewest fields needed to reproduce your problem, please - use
fake field names if you're worried about confidentiality, as long as the
datatypes are correct), your actual query statement (again, cut it down to
the minimum needed to reproduce your problem), some sample data, and the
parameter values you pass to reproduce the problem.

Bob Barrows

 

[Bob Barrows]

Bob,

It looks like adding sqr() changed the order in which the parameters
should be passed to the query from ASP.

How do you fetermine in which order the parameters hould be listed in
the query like
objCom.CommandText=QueryName & "'" & param1 & "'," & "'" & param2 &"'"
if there are more then one parameter in the stored query?
It does not seem to foloow the order in which the parameters appear
in the query.

The best way is to run the query in Access and take note of the order in
which Access prompts you for parameter values. IIRC, the order should be:

1. Without the PARAMETERS statement, the order will be the order in which
they appear in the query
2. With the PARAMETERS statement, the order will be the order in which they
are declared in the PARAMETERS statement

HTH,
Bob Barrows

 

[Guest]

Thanks, Bob

The actual query stored in Access is (I do it without [], except for the
parameters, but Access inserts them here and there):
SELECT
Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
AS Expr1
FROM competition, analog, t_main
WHERE (((analog.af_product)=[] And (analog.af_product)=[t_main].[pr_code])
AND ((analog.direct_analog)=Yes) AND
(([analogs].[competitor_id])=[competition].[id]));

Althought the first parameter is RUB, when running in Access it is promted
as the second.
It looks like parameters have certain hierarchy and the one in WHERE takes
precedence.

My conclusions in the previous message seem to be premature. When I changed
the parameter order, the code produced no error messages (defenitely it
should as my 1st parameter is a text string). Instead it quetly returns
value of type 0 (Empty(uninitiated)).
This beats me completely. If it accepted the parameter, then the query
should have returned an empty recordset which is type 1 (Null, no valid
data) - I am getting this type when there is no match to a parameter.

PS. Thank you for your preparedness to look into my database - there is no
confidential data and I will be happy to let you have it, yet in the new
circumstances it might be irrelevant?

 

[Bob Barrows]

Thanks, Bob

The actual query stored in Access is (I do it without [], except for
the parameters, but Access inserts them here and there):
SELECT
Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
AS Expr1
FROM competition, analog, t_main
WHERE (((analog.af_product)=[] And

Is this a typo? What is meant by []?

(analog.af_product)=[t_main].[pr_code]) AND
((analog.direct_analog)=Yes) AND
(([analogs].[competitor_id])=[competition].[id]));

Althought the first parameter is RUB, when running in Access it is
promted as the second.
It looks like parameters have certain hierarchy and the one in WHERE
takes precedence.

Is there no PARAMETERS statement? I thought you had created that?

My conclusions in the previous message seem to be premature. When I
changed the parameter order, the code produced no error messages
(defenitely it should as my 1st parameter is a text string). Instead
it quetly returns value of type 0 (Empty(uninitiated)).
This beats me completely. If it accepted the parameter, then the query
should have returned an empty recordset which is type 1 (Null, no
valid data) - I am getting this type when there is no match to a
parameter.

PS. Thank you for your preparedness to look into my database - there
is no confidential data and I will be happy to let you have it, yet
in the new circumstances it might be irrelevant?

I don't need the whole database, just the structures of the tables involved
(relevant fields only) and a few rows of sample data for each table,
preferably in the form of INSERT...VALUES statements. I'll reconstruct the
tables in my own database and attempt to reproduce your problem.

Bob Barrows

 

[Guest]

Is this a typo? What is meant by []?

No, this is how I do parameterized queries in Access.
[] means a parameter to be enteres during runtime.
Is this wrong?
Then I hope this is the reason for the error. How it should be?

Thanks, Bob

The actual query stored in Access is (I do it without [], except for
the parameters, but Access inserts them here and there):
SELECT
Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
AS Expr1
FROM competition, analog, t_main
WHERE (((analog.af_product)=[] And

Is this a typo? What is meant by []?

(analog.af_product)=[t_main].[pr_code]) AND
((analog.direct_analog)=Yes) AND
(([analogs].[competitor_id])=[competition].[id]));

Althought the first parameter is RUB, when running in Access it is
promted as the second.
It looks like parameters have certain hierarchy and the one in WHERE
takes precedence.

Is there no PARAMETERS statement? I thought you had created that?

My conclusions in the previous message seem to be premature. When I
changed the parameter order, the code produced no error messages
(defenitely it should as my 1st parameter is a text string). Instead
it quetly returns value of type 0 (Empty(uninitiated)).
This beats me completely. If it accepted the parameter, then the query
should have returned an empty recordset which is type 1 (Null, no
valid data) - I am getting this type when there is no match to a
parameter.

PS. Thank you for your preparedness to look into my database - there
is no confidential data and I will be happy to let you have it, yet
in the new circumstances it might be irrelevant?

I don't need the whole database, just the structures of the tables involved
(relevant fields only) and a few rows of sample data for each table,
preferably in the form of INSERT...VALUES statements. I'll reconstruct the
tables in my own database and attempt to reproduce your problem.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows]

Is this a typo? What is meant by []?

No, this is how I do parameterized queries in Access.
[] means a parameter to be enteres during runtime.
Is this wrong?
Then I hope this is the reason for the error. How it should be?

I always give my parameters a name.It makes the queries a little more
self-documenting. I guess it can work, but I would never use [] as a
parameter.

Bob Barrows

 

[Guest]

Actually this is how parameterised queries were described in Access97 manual
and I've been using it like that since then.

I was sure that [] was not a parameter name, but a sign which means place
for a parameter. I was alway wondering what if there are more then one
parameter - and I have these, all the parameters were set as []. Also []
does not show up as a parameter name at the prompt, when running the query
in Access.

But when I tried to use a variable name instead of [] or within [], when
running this query in Access, in that query I am prompted for three
parameters. The first one is with no name, and the next two with the names
assigned in the query - why so?

Is this a typo? What is meant by []?

No, this is how I do parameterized queries in Access.
[] means a parameter to be enteres during runtime.
Is this wrong?
Then I hope this is the reason for the error. How it should be?

I always give my parameters a name.It makes the queries a little more
self-documenting. I guess it can work, but I would never use [] as a
parameter.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Guest]

I which circumstances AVG agregate finction returns 0 type?

Thanks, Bob

The actual query stored in Access is (I do it without [], except for
the parameters, but Access inserts them here and there):
SELECT
Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
AS Expr1
FROM competition, analog, t_main
WHERE (((analog.af_product)=[] And

Is this a typo? What is meant by []?

(analog.af_product)=[t_main].[pr_code]) AND
((analog.direct_analog)=Yes) AND
(([analogs].[competitor_id])=[competition].[id]));

Althought the first parameter is RUB, when running in Access it is
promted as the second.
It looks like parameters have certain hierarchy and the one in WHERE
takes precedence.

Is there no PARAMETERS statement? I thought you had created that?

My conclusions in the previous message seem to be premature. When I
changed the parameter order, the code produced no error messages
(defenitely it should as my 1st parameter is a text string). Instead
it quetly returns value of type 0 (Empty(uninitiated)).
This beats me completely. If it accepted the parameter, then the query
should have returned an empty recordset which is type 1 (Null, no
valid data) - I am getting this type when there is no match to a
parameter.

PS. Thank you for your preparedness to look into my database - there
is no confidential data and I will be happy to let you have it, yet
in the new circumstances it might be irrelevant?

I don't need the whole database, just the structures of the tables involved
(relevant fields only) and a few rows of sample data for each table,
preferably in the form of INSERT...VALUES statements. I'll reconstruct the
tables in my own database and attempt to reproduce your problem.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows]

Actually this is how parameterised queries were described in Access97
manual and I've been using it like that since then.

I was sure that [] was not a parameter name, but a sign which means
place for a parameter. I was alway wondering what if there are more
then one parameter - and I have these, all the parameters were set as
[]. Also [] does not show up as a parameter name at the prompt, when
running the query in Access.

But when I tried to use a variable name instead of [] or within [],
when running this query in Access, in that query I am prompted for
three parameters. The first one is with no name, and the next two
with the names assigned in the query - why so?

The ones where you only want a single prompt should all be given the same
name

Select [field1] * [parm1], [field2] * [parm1] ...

You will only receive a single prompt for this parameter since both
placeholders have the same name.


Select [field1] * [parm1], [field2] * [parm1], ...
WHERE [field3] = [parm2]

You will receive 2 prompts when running this query: once to get the value
for parm1, and then to get the value for parm2.

I tend to give them more meaningful names, just for readability. I always
prefix them with "p". [pStartDate], [pDiscount] etc.

HTH,
Bob Barrows

 

[Bob Barrows]

I which circumstances AVG agregate finction returns 0 type?

I'm not sure where you are seeing this "type". Could you show me your code
where you determine it's type is 0?

Could it be that it contains Null when the type is 0? Avg will return Null
when no records meet the selection criteria.

Bob Barrows