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]

I've inserted your data into the tables and I still have no problems running
the query.

Try my code and see if you have issues.

What provider are you using to open your connection?

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, I will and report. Actually you've done the job I was supposed
to do myself - appreciated.

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

I've inserted your data into the tables and I still have no problems running the query.

Try my code and see if you have issues.

What provider are you using to open your connection?

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.

Microsoft.Jet.O LEDB.4.0

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

I've inserted your data into the tables and I still have no problems running the query.

Try my code and see if you have issues.

What provider are you using to open your connection?

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,
When I run the query in Access I keep having three prompts for parameters.
The 1st is for un-named parameter
The 2nd for rub
the 3rd for pCode

And it returns a value only if I enter to the first un-named parameter the
same value as for pCode. Otherwise it returns emty cell (which perhaps it
taken by VarType as 0 type)

I think I need to understand this bit which to me seems to be the root of
the problem
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:ur******** ******@tk2msftn gp13.phx.gbl...

I've inserted your data into the tables and I still have no problems running the query.

Try my code and see if you have issues.

What provider are you using to open your connection?

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:

Bob,
When I run the query in Access I keep having three prompts for
parameters. The 1st is for un-named parameter
The 2nd for rub
the 3rd for pCode

Then you're running a different query than the one you showed me.

I don't have an unnamed parameter in my query. Here are the two versions of
the query that I tested (I prefer the second syntax ... actually, I'm going
to make another change - I never liked that "Expr" column alias - let's
change that to something meaningful):

SELECT
Avg(competition .price_rub*Sqr( t_main.per_pack/competition.qua ntity))/[rub]
AS AvgCalculation
FROM competition, analog, t_main
WHERE (((analog.af_pr oduct)=[pCode] AND
(analog.af_prod uct)=[t_main].[pr_code])
AND ((analog.direct _analog)=Yes) AND
(([analog].[competitor_id])=[competition].[id]))

SELECT Avg(c.price_rub *Sqr(t.per_pack/c.quantity))/[rub] AS AvgCalculation
FROM (competition AS c INNER JOIN analog AS a ON c.id = a.competitor_id )
INNER JOIN t_main AS t ON a.af_product = t.pr_code
WHERE a.af_product=[t_main].[pr_code] AND a.direct_analog )=Yes

Paste this sql into a query builder window, switch to Design View and check
out what happened to the table objects in the upper pane.
If you still have an unnamed parameter, I suspect you should replace it with
"[pCode.]" (no quotes)
I think you will find coding these things a whole lot simpler if you stop
using unnamed 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,
I cut-and-pasted your query into Access - same.
One of the explanations might be that I have a typo somewhere so that the
tables-fields names do not match those in the query. But then it would
report that there is no such a field, would not it?
Anyway, I double-checked it - no joy. And after all, the whole thing was
working perfectly before I added the sqr(). Still it does not explain three
prompts.

BTW, did you noticed my remark, that the code does not complain about a
negative number being passed to sqr()? Which probably means that sqr() is
not executed

Also I am confused about used of [] in the syntax. Even in your text they
seem to be used inconsistently. What is thier purpose? Can I do without
them? Actually I tested other queries and I see that they are not necessary,
but for some reasom people use them.
Regarding "Expr" column alias - it does not seem to be relevant - it is
just a fieldname. If I omit the hole as Expr nothing changes except Access
uses some dafault name for the field. This only might be relevant is the
result of that query is used by some other query, might it not?
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** *******@TK2MSFT NGP10.phx.gbl.. .

aa wrote:

Bob,
When I run the query in Access I keep having three prompts for
parameters. The 1st is for un-named parameter
The 2nd for rub
the 3rd for pCode
Then you're running a different query than the one you showed me.

I don't have an unnamed parameter in my query. Here are the two versions

of the query that I tested (I prefer the second syntax ... actually, I'm going to make another change - I never liked that "Expr" column alias - let's
change that to something meaningful):

SELECT
Avg(competition .price_rub*Sqr( t_main.per_pack/competition.qua ntity))/[rub]
AS AvgCalculation
FROM competition, analog, t_main
WHERE (((analog.af_pr oduct)=[pCode] AND
(analog.af_prod uct)=[t_main].[pr_code])
AND ((analog.direct _analog)=Yes) AND
(([analog].[competitor_id])=[competition].[id]))

SELECT Avg(c.price_rub *Sqr(t.per_pack/c.quantity))/[rub] AS AvgCalculation
FROM (competition AS c INNER JOIN analog AS a ON c.id = a.competitor_id )
INNER JOIN t_main AS t ON a.af_product = t.pr_code
WHERE a.af_product=[t_main].[pr_code] AND a.direct_analog )=Yes

Paste this sql into a query builder window, switch to Design View and check out what happened to the table objects in the upper pane.
If you still have an unnamed parameter, I suspect you should replace it with "[pCode.]" (no quotes)
I think you will find coding these things a whole lot simpler if you stop
using unnamed 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]

aa wrote:

Bob,
I cut-and-pasted your query into Access - same.
One of the explanations might be that I have a typo somewhere so that
the tables-fields names do not match those in the query.
I did have to modify the table designs so that the column names in the sql
matched the column names you provided in your email
But then it
would report that there is no such a field, would not it?
It did when I initially tried your query, which lead me to change the field
names. I received extra prompts for the column names that did not match the
columns in the tables.

I guess I'm going to need your database after all. Can you zip it up and
send it to my email address (remove the "NO SPAM" from my reply-to address)?

Anyway, I double-checked it - no joy. And after all, the whole thing
was working perfectly before I added the sqr(). Still it does not
explain three prompts.

BTW, did you noticed my remark, that the code does not complain about
a negative number being passed to sqr()? Which probably means that
sqr() is not executed
One issue-at-a-time :-) Let's get your parameters working right.

Also I am confused about used of [] in the syntax. Even in your text
they seem to be used inconsistently. What is thier purpose? Can I do
without them? Actually I tested other queries and I see that they are
not necessary, but for some reasom people use them.
They are needed when using non-standard names for database objects. For
example, if you had a column name with a space in it, such as This Column,
in a table named This Table, this sql statement would crash:

Select This Column from This Table

The brackets prevent the parser from attempting to evaluate the non-standard
names, allowing them to be sent to the query engine as-is:

Select [This Column] from [This Table]

They may also be required if you've made the mistake of using a reserved
keyword for a column or table name (see here for the list of reserved
keywords: http://www.aspfaq.com/show.asp?id=2080)



Regarding "Expr" column alias - it does not seem to be relevant - it
is just a fieldname. If I omit the hole as Expr nothing changes
except Access uses some dafault name for the field. This only might
be relevant is the result of that query is used by some other query,
might it not?

I did not say it was relevant: I just was explaining my personal preference.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks, Bob,
I need to clean the DB a bit - it is full of irrelevant to this matter
things - and let you have it.
Meanwhile if you are sayin you have similar prompts problem and sorted it
out by changeing the table/fields names - perhaps you let me know which onse
were wrong?

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

aa wrote:

Bob,
I cut-and-pasted your query into Access - same.
One of the explanations might be that I have a typo somewhere so that
the tables-fields names do not match those in the query.
I did have to modify the table designs so that the column names in the sql
matched the column names you provided in your email

But then it
would report that there is no such a field, would not it?

It did when I initially tried your query, which lead me to change the

field names. I received extra prompts for the column names that did not match the columns in the tables.

I guess I'm going to need your database after all. Can you zip it up and
send it to my email address (remove the "NO SPAM" from my reply-to address)?

Anyway, I double-checked it - no joy. And after all, the whole thing
was working perfectly before I added the sqr(). Still it does not
explain three prompts.

BTW, did you noticed my remark, that the code does not complain about
a negative number being passed to sqr()? Which probably means that
sqr() is not executed
One issue-at-a-time :-) Let's get your parameters working right.

Also I am confused about used of [] in the syntax. Even in your text
they seem to be used inconsistently. What is thier purpose? Can I do
without them? Actually I tested other queries and I see that they are
not necessary, but for some reasom people use them.

They are needed when using non-standard names for database objects. For
example, if you had a column name with a space in it, such as This Column,
in a table named This Table, this sql statement would crash:

Select This Column from This Table

The brackets prevent the parser from attempting to evaluate the

non-standard names, allowing them to be sent to the query engine as-is:

Select [This Column] from [This Table]

They may also be required if you've made the mistake of using a reserved
keyword for a column or table name (see here for the list of reserved
keywords: http://www.aspfaq.com/show.asp?id=2080)

Regarding "Expr" column alias - it does not seem to be relevant - it
is just a fieldname. If I omit the hole as Expr nothing changes
except Access uses some dafault name for the field. This only might
be relevant is the result of that query is used by some other query,
might it not?
I did not say it was relevant: I just was explaining my personal

preference.
HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

aa wrote:

Thanks, Bob,
I need to clean the DB a bit - it is full of irrelevant to this matter
things - and let you have it.
Meanwhile if you are sayin you have similar prompts problem and
sorted it out by changeing the table/fields names - perhaps you let
me know which onse were wrong?

I don't remember. I knew which ones they were because they (the defective
names) appeared in the parameter prompts.

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,
Your suggestion to let you hev my DB already had a positive outcome. While
cleaning the DB I found that I confused the name of the query with the name
of the table (the difference was just one letter) - so I sorted out the
problem of too many prompts.

However this have not sorted out the original problem.
I added the PARAMETERS clouse to make sure the I pass the parameters in the
right order.
I still have the same error. But running the query from ASP affected the
syntax of the query;
Originally I made it
PARAMETERS pCode String, rub Double.
After running ASP code it became
PARAMETERS pCode Text ( 255 ), rub IEEEDouble

What is this about and if this has to do with the error?

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

aa wrote:

Thanks, Bob,
I need to clean the DB a bit - it is full of irrelevant to this matter
things - and let you have it.
Meanwhile if you are sayin you have similar prompts problem and
sorted it out by changeing the table/fields names - perhaps you let
me know which onse were wrong?

I don't remember. I knew which ones they were because they (the defective
names) appeared in the parameter prompts.

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.