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******@NOyahoo.SPAMcom> wrote in message
news:u3**************@tk2msftngp13.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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@tk2msftngp13.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|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.

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()

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:er**************@TK2MSFTNGP12.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|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:

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


"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:#p**************@TK2MSFTNGP11.phx.gbl... aa wrote:

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

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.

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" <re******@NOyahoo.SPAMcom> wrote in message
news:%2******************@TK2MSFTNGP11.phx.gbl...

aa wrote:

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

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

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

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" <re******@NOyahoo.SPAMcom> wrote in message
news:e8**************@TK2MSFTNGP11.phx.gbl...

aa wrote:

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

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

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.

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?
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

aa wrote:

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]

aa wrote:

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.

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?

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ed****************@tk2msftngp13.phx.gbl...

aa wrote:

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.

I which circumstances AVG agregate finction returns 0 type?

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

aa wrote:

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]

aa wrote:

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

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

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

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" <re******@NOyahoo.SPAMcom> wrote in message
news:ed****************@tk2msftngp13.phx.gbl...

aa wrote:

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.

"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" <re******@NOyahoo.SPAMcom> wrote in message
news:Op**************@TK2MSFTNGP12.phx.gbl...

aa wrote:

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

"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" <re******@NOyahoo.SPAMcom> wrote in message
news:Op**************@TK2MSFTNGP12.phx.gbl...

aa wrote:

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

"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

--
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. 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" <re******@NOyahoo.SPAMcom> wrote in message
news:e$**************@TK2MSFTNGP09.phx.gbl...

aa wrote:

"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

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

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

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

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

52.9739

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ue**************@TK2MSFTNGP09.phx.gbl...

aa wrote:

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

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" <re******@NOyahoo.SPAMcom> wrote in message
news:O7**************@TK2MSFTNGP11.phx.gbl...

aa wrote:

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

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

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******@NOyahoo.SPAMcom> wrote in message
news:ur**************@tk2msftngp13.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.OLEDB.4.0

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ur**************@tk2msftngp13.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******@NOyahoo.SPAMcom> wrote in message
news:ur**************@tk2msftngp13.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.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,
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******@NOyahoo.SPAMcom> wrote in message
news:%2***************@TK2MSFTNGP10.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.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]

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******@NOyahoo.SPAMcom> wrote in message
news:Op**************@TK2MSFTNGP12.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******@NOyahoo.SPAMcom> wrote in message
news:u$**************@TK2MSFTNGP10.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.