473,544 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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?
Jul 19 '05 #1
39 7847
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.
Jul 19 '05 #2
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.

Jul 19 '05 #3
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.
Jul 19 '05 #4
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.

Jul 19 '05 #5
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.
Jul 19 '05 #6
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.

Jul 19 '05 #7
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.
Jul 19 '05 #8
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.

Jul 19 '05 #9
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.
Jul 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1717
by: Christos TZOTZIOY Georgiou | last post by:
Hi all, this post contains at the end a handy module that I've used quite often when I wanted to analyse the occasional complex expression and how it was to be evaluated. The function analyse_expression is called with a single string argument containing an expression. Names are allowed (actually, preferred over numbers ;-), since the...
1
7975
by: Terencetrent | last post by:
I am trying to format a query expression drawn from a dialog box as percent. The original statement to get the value for the query is as follows: New%markup: !! The dialog box looks the values up from a table called NewMarkup. It appears in the dialog box with a percent format. However, when I try to call that value in my query it...
1
3226
by: Penguin | last post by:
For some odd reason, I am trying to do some complex time calculations that I want to let the user define. So I set up a table with EventID EventType EventExpression 1 SolarNoon datediff("n",Sunrise,Sunset) etc. This is supposed to be evaluated with TimeofNoon=Eval(EventExpression)
3
3287
by: MLH | last post by:
Am repeating question with different subject heading, perhaps stating more clearly my problem... I have an A97 query (qryVehiclesNowners2) that has a table field in it named . Depending on the selections made in a number of criteria choices on a form, a field on the form will have string values in it like... Between #12/1/2004# And...
1
3669
by: Andrew | last post by:
How can I create a property or function for a typed datasets column? By this I need to add a new column (element) to the table or override an existing column. For instance I have an Invoice table that needs to have a Status property. This status property has to read the other elements in the table to determine what the status is and return a...
14
4977
by: cvollberg via AccessMonster.com | last post by:
Good morning, I need a little help, I keep getting this error when I run this report. I have narrowed it down to the query (sql view) listed below. the error is: "This expression is typed incorrectly, or it is too complex to be evaluated" SELECT ActiveBranch., -Sum(/) AS , -Sum(/) AS , -Sum( /)*10000 AS AncillaryFeeBpsMTD, -Sum(/...
9
2126
by: jessicaeatworld | last post by:
Hi, I'm using Access 2003 on Windows XP. My Error: 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. I'm guessing that one of these calcs is the problem: ...
4
1673
by: cmartin1986 | last post by:
I am trying to write a sql query to return a count of records from a user defined supplier in weekly buckets going back from a user defined date. I get an error saying "This expression is typed incorrectly or it is too complex to be evaluated...." When I orginally wrote this it would simply start from Date() and go back weekly and still had a...
1
4998
by: Coll | last post by:
I'm receiving this message when running a query... This expression is typed incorrectly or 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. Here's the background. I have a query. I've figured out...
0
7359
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7598
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7360
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5288
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3398
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
982
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
651
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.