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

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]

aa wrote:

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.

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?

"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:u3******** ******@tk2msftn gp13.phx.gbl...

aa wrote:

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]

aa wrote:

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.

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


"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

aa wrote:

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]

aa wrote:

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|Parameter s

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.

Thnanks, Bob
I run the query in ASP using:

objCom.CommandT ext=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()

"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:er******** ******@TK2MSFTN GP12.phx.gbl...

aa wrote:

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|Parameter s

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]

aa wrote:

Thnanks, Bob
I run the query in ASP using:

objCom.CommandT ext=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.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS

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

Set objRS(3) = server.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS(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.createob ject("adodb.rec ordset")
objConn.QueryNa me CInt(param1),CD bl(param2),objR S

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
--
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.

[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.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS
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 ?


"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:#p******** ******@TK2MSFTN GP11.phx.gbl... aa wrote:

Thnanks, Bob
I run the query in ASP using:

objCom.CommandT ext=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.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS

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

Set objRS(3) = server.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS(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.createob ject("adodb.rec ordset")
objConn.QueryNa me CInt(param1),CD bl(param2),objR S

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
--
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]

aa wrote:

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.createob ject("adodb.rec ordset")
objConn.QueryNa me param1,param2,o bjRS
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
--
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.