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

[Guest]

The database contains comparetive data on competition for a certain product
range.

t_main table contains my product range (codes, parameters, prices)
competition table contains similar data on somebody else competitive
products
analog table indicates which compatitive products are analogues to which of
my products.(many-to-many relationship)

I understand you need only those fields which are involved in the query

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

These are:

competition
id - Autonumber Long Integer
price_rub - currency, fixed with dot as decimal point eg. 100.00
quantity - Number Long integer eg.400
neither are key or part of the key

analog
competitor_id - Number Long integer eg. 75
af_product -text eg. AAA000
direct_analog - yes/no
the first two make a composite key

t_main
pr_code - text eg. AAA000 (key field)
per_pack - Number Long integer eg. 300

Parameter rub (the exchange rate) is stored in an ASP Application scope
variable and returns type 5 (double precision floating point number)

Parameter [] is taken from another recordset containg a value from
t_main.pr_code, i.e. something like AAA000

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'm not sure where you are seeing this "type". Could you show me your code
where you determine it's type is 0?"

response.write VarType(objRS(0))
And it returns 0 for all 85 records in the table.
When there is no matches, it returns 1. Actually my code based on that. Now
matches means no analogues or no analog price recorded and the code returns
"not available". Again the whole thing has been working fine until I desided
to apply square root to the ratio. Is there a chance that the database
somehow consideres the numbers negative?
Just as a test I changed one value in competition.quantity to negative and
.... nothing changed as if it suxxessfully extracted the square root our of
the negative number.
I think I started halucinating, so I have to go to bed now.

PS. I added the parameter clouse. It changed nothing, so I removed it.

 

[Guest]

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

response.write VarType(objRS(0))
And it returns 0 for all 85 records in the table.
When there is no matches, it returns 1. Actually my code based on that. Now
matches means no analogues or no analog price recorded and the code returns
"not available". Again the whole thing has been working fine until I desided
to apply square root to the ratio. Is there a chance that the database
somehow consideres the numbers negative?
Just as a test I changed one value in competition.quantity to negative and
.... nothing changed as if it suxxessfully extracted the square root our of
the negative number.
I think I started halucinating, so I have to go to bed now.

PS. I added the parameter clouse. It changed nothing, so I removed it.

 

[Bob Barrows]

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

response.write VarType(objRS(0))

I have never used this function with my data retrieval code. I use rs.EOF to
detect whether any records were returned, and if records were returned, I
check the contents of the fields usually using the len() function.

And it returns 0 for all 85 records in the table.
When there is no matches, it returns 1. Actually my code based on
that. Now matches means no analogues or no analog price recorded and
the code returns "not available". Again the whole thing has been
working fine until I desided to apply square root to the ratio. Is
there a chance that the database somehow consideres the numbers
negative?
Just as a test I changed one value in competition.quantity to
negative and ... nothing changed as if it suxxessfully extracted the
square root our of the negative number.
I think I started halucinating, so I have to go to bed now.

I can't help you any further without seeing the datatypes of the fields
involved in the query, a few rows of sample data -
an INSERT tablename(<column_list>) VALUES (<value_list) statement for each
row of sample data would be best,
and the parameter values you are attempting to pass.

I know you want to keep trying to solve it yourself seeing as how you are so
close (I fall into the same trap myself), but at this point, you really need
another set of eyes looking at it first-hand.

Bob Barrows

 

[Guest]

Thanks, Bob.

1. I use rs.EOF

I have to call aggregate functions to determine min, max and average antway.
And I decied to utilise the by-product of these functions (vartype returnes)
rather then run another query and evaluating rs.EOF
But this should not matter - just another way to do the same.

2. I can't help you any further without seeing the datatypes of the fields

I've posted it yesterday - is it not sufficient?
It's difficult for me to post it as an INSERT as I never used these
statement before (I define the tables in Access and then apload it),. and
will have to practice it to make sure that I send you the correct one.
So I sent it like this:

The database contains comparetive data on competition for a certain product
range.

t_main table contains my product range (codes, parameters, prices)
competition table contains similar data on somebody else competitive
products
analog table indicates which compatitive products are analogues to which of
my products.(many-to-many relationship)

I understand you need only those fields which are involved in the query

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

These are:

competition
id - Autonumber Long Integer
price_rub - currency, fixed with dot as decimal point eg. 100.00
quantity - Number Long integer eg.400
neither are key or part of the key

analog
competitor_id - Number Long integer eg. 75
af_product -text eg. AAA000
direct_analog - yes/no
the first two make a composite key

t_main
pr_code - text eg. AAA000 (key field)
per_pack - Number Long integer eg. 300

Parameter rub (the exchange rate) is stored in an ASP Application scope
variable and returns type 5 (double precision floating point number)

Parameter [] is taken from another recordset containg a value from
t_main.pr_code, i.e. something like AAA000

 

[Bob Barrows]

Thanks, Bob.

1. I use rs.EOF

I have to call aggregate functions to determine min, max and average
antway. And I decied to utilise the by-product of these functions
(vartype returnes) rather then run another query and evaluating rs.EOF
But this should not matter - just another way to do the same.

2. I can't help you any further without seeing the datatypes of the
fields

I've posted it yesterday - is it not sufficient?

There were 20 messages yesterday. I must have missed it.

It's difficult for me to post it as an INSERT as I never used these

Fill in the blanks:

Sample row 1:
INSERT t_main (pr_code, per_pack)
VALUES ('AAA000', 300)
Sample row 2:
INSERT t_main (pr_code, per_pack)
VALUES ('______', ___)

Sample row 1:
INSERT competition (price_rub,quantity)
VALUES (100.00,400)
Sample row 1:
INSERT competition (price_rub,quantity)
VALUES (____, _____)

etc. Note that I did not include the autonumber field in the column or value
lists.


I'll get back to you later.

Bob Barrows

 

[Bob Barrows]

Parameter rub (the exchange rate) is stored in an ASP Application
scope variable and returns type 5 (double precision floating point
number)

What is a typical value for this parameter?

Bob Barrows

 

[Bob Barrows]

aa wrote:

OK, I recreated your tables in my database and put one row of data (using
the example data you provided) in each table. I used this sql*:

SELECT
Avg(competition.price_rub*Sqr(t_main.per_pack/competition.quantity))/[rub]
AS Expr1
FROM competition, analog, t_main
WHERE (((analog.af_product)=[pCode] AND
(analog.af_product)=[t_main].[pr_code])
AND ((analog.direct_analog)=Yes) AND
(([analog].[competitor_id])=[competition].[id]))


to create a saved query called qAvg. I then ran this code in asp to try and
cause your error:

set cn=server.CreateObject("adodb.connection")
cn.Open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & Server.MapPath("dbfiles/db15.mdb")
set rs = server.createobject("adodb.recordset")
cn.qAvg .25,"AAA000", rs
if not rs.eof then ar = rs.GetRows
rs.close: set rs = nothing
cn.close: set cn = nothing
if isarray(ar) then
response.write ar(0,0)
erase ar
else
response.write "No records"
end if

No problems. Please try it yourself (substituting your database connection
info of course) and see if it works.

Bob Barrows

*I would have used this syntax, myself:
SELECT Avg(c.price_rub*Sqr(t.per_pack/c.quantity))/[rub] AS Expr1
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

 

[Guest]

52.9739

What is a typical value for this 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]

Hete it goes.
As we cannot indicate Autonumber fiels in competition, but it is used in
analog, in analog I suggested the the autonumber values for the first two
rows in competition are 1 and 2 respectively.


t_main
Sample row 1:
INSERT t_main (pr_code, per_pack)
VALUES ('AAA000', 200)
Sample row 2:
INSERT t_main (pr_code, per_pack)
VALUES ('BBB000',300)

competition
Sample row 1:
INSERT competition (price_rub,quantity)
VALUES (100.00,400)
Sample row 2:
INSERT competition (price_rub,quantity)
VALUES (120.00, 500)

analog
Sample row 1:
INSERT analog (competitor_id,af_product,direct_analog)
VALUES (1,AAA000,yes)
Sample row 2:
INSERT analog (competitor_id,af_product,direct_analog)
VALUES (2,"AAA000",yes)

I expect that query to return:

(100.00*sqr(200/400) + 120.00*sqr(200/500))/2/52.9739

 

[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

 

[Guest]

Thanks, Bob, I will and report. Actually you've done the job I was supposed
to do myself - appreciated.

 

[Guest]

Microsoft.Jet.OLEDB.4.0

 

[Guest]

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]

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.quantity))/[rub]
AS AvgCalculation
FROM competition, analog, t_main
WHERE (((analog.af_product)=[pCode] AND
(analog.af_product)=[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

 

[Guest]

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,
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.quantity))/[rub]
AS AvgCalculation
FROM competition, analog, t_main
WHERE (((analog.af_product)=[pCode] AND
(analog.af_product)=[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]

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

 

[Guest]

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

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

 

[Guest]

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?