MIN(some_field/[])

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

From another application (ASP) I run a query using MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
 
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()
 
Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax to
allow the query to take a parameter for MIN function?


Paul Overway said:
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


From another application (ASP) I run a query using MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
 
The QueryDefs collection is part of DAO. Since you posted to Access groups,
Paul's logical assumption is that you're communicating from ASP to a Jet
database (i.e.: an MDB file). He's also assuming, though, that you have
Access installed on the IIS server, since I don't believe there's any other
way to instantiate the database object so that you can use the DAO objects.

If you're using ADO, you should be able to use its Parameters collection.
Sorry, but I don't have an example handy.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax to
allow the query to take a parameter for MIN function?


Paul Overway said:
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


From another application (ASP) I run a query using MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
What are you using for data access? DAO or ADO? If you're writing ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO examples on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax to
allow the query to take a parameter for MIN function?


Paul Overway said:
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


From another application (ASP) I run a query using MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
You can instantiate DBEngine and other DAO objects via VB Script....Access
is not required. So, he can do it using ADO or DAO.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Douglas J. Steele said:
The QueryDefs collection is part of DAO. Since you posted to Access groups,
Paul's logical assumption is that you're communicating from ASP to a Jet
database (i.e.: an MDB file). He's also assuming, though, that you have
Access installed on the IIS server, since I don't believe there's any other
way to instantiate the database object so that you can use the DAO objects.

If you're using ADO, you should be able to use its Parameters collection.
Sorry, but I don't have an example handy.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax to
allow the query to take a parameter for MIN function?


Paul Overway said:
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


Paul Overway said:
What are you using for data access? DAO or ADO? If you're writing ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO examples on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax to
allow the query to take a parameter for MIN function?


Paul Overway said:
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Hi,


myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"


Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

ie: use the stored proc as if it was a function, but without ( ) around the
arguments list.


It works only with Jet 4.0 ( Access 2000 or later), and only with ADO.


The syntax is

CREATE PROC procName ( listOfArguments) AS sqlStatement


You can drop the procedure like this:

DROP PROC procName


You can use the complete word PROCEDURE instead of just PROC. Note that
you can also use an action query, not just a SELECT query, as sql statement.





Hoping it may help,
Vanderghast, Access MPV


Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


Paul Overway said:
What are you using for data access? DAO or ADO? If you're writing ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO
Click to expand...
examples
on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google
Click to expand...
syntax
to
allow the query to take a parameter for MIN function?


Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Vanderghast,

You seem to tell me eaxctly what I need, but I have difficulties to digest
it.

1.
myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"

You mean this will create query called toto containing the following SQL:
SELECT MIN(whatever/[argument]) FROM somewhere; ?

I run this line of code and no query was created. Also no error was
generated.
Access does not support stored procedures - does this matter?


2. Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

This syntax confuses me.
Why not
Set myAdoRst = myConnection.Execute( "toto 222 ") ?
Why do I need the second EXECUTE ?



Michel Walsh said:
Hi,


myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"


Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

ie: use the stored proc as if it was a function, but without ( ) around the
arguments list.


It works only with Jet 4.0 ( Access 2000 or later), and only with ADO.


The syntax is

CREATE PROC procName ( listOfArguments) AS sqlStatement


You can drop the procedure like this:

DROP PROC procName


You can use the complete word PROCEDURE instead of just PROC. Note that
you can also use an action query, not just a SELECT query, as sql statement.





Hoping it may help,
Vanderghast, Access MPV


Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


Paul Overway said:
What are you using for data access? DAO or ADO? If you're writing ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO
Click to expand...
examples
on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the
Click to expand...
syntax
to
allow the query to take a parameter for MIN function?


Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Hi,


The CREATE PROC would create a stored procedure, which Jet 4.0 accepts
( but they are limited to one sql statement ). The query would be listed in
Access 2002 or in Access 2003, but not in Access 2000, if you refresh the
list of the queries (move to see the list of tables, and move back to see
the list of queries... you should see toto in Access 2002 and 2003) . Even
if it is not listed with Access 2000, the stored proc would work for that
version too.

The Execute in the Connection.Execute is a method that belong to the ADO
connection. The EXECUTE in "EXECUTE toto 222" is a SQL keyword. So, one
execute indicates a method to a connection object, the other, is part of the
SQL Syntax. Just like two dogs can be called toto, that does not mean the
two dogs are just one, or that one of the dog does not need to be there it
the other is... :-) As dog, they are just unaware of the presence of each
other.


Hoping it may help,
Vanderghast, Access MVP



Vanderghast,

You seem to tell me eaxctly what I need, but I have difficulties to digest
it.

1.
myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"

You mean this will create query called toto containing the following SQL:
SELECT MIN(whatever/[argument]) FROM somewhere; ?

I run this line of code and no query was created. Also no error was
generated.
Access does not support stored procedures - does this matter?


2. Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

This syntax confuses me.
Why not
Set myAdoRst = myConnection.Execute( "toto 222 ") ?
Why do I need the second EXECUTE ?



Michel Walsh said:
Hi,


myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"


Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

ie: use the stored proc as if it was a function, but without ( ) around the
arguments list.


It works only with Jet 4.0 ( Access 2000 or later), and only with ADO.


The syntax is

CREATE PROC procName ( listOfArguments) AS sqlStatement


You can drop the procedure like this:

DROP PROC procName


You can use the complete word PROCEDURE instead of just PROC. Note that
you can also use an action query, not just a SELECT query, as sql statement.





Hoping it may help,
Vanderghast, Access MPV


Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


What are you using for data access? DAO or ADO? If you're writing ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO examples
on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the syntax
to
allow the query to take a parameter for MIN function?


Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Michel for detailed reply.
I am using Access2000 and I wanted to add this query to the mdb file on my
local PC and upload it on the server.
For that purpose the statement I guessed
SELECT MIN(whatever/[argument]) FROM somewhere;
- is it correct?

Regarding the rest - I need time to digest it and play with it. Perhaps I'll
return here to morrow

Michel Walsh said:
Hi,


The CREATE PROC would create a stored procedure, which Jet 4.0 accepts
( but they are limited to one sql statement ). The query would be listed in
Access 2002 or in Access 2003, but not in Access 2000, if you refresh the
list of the queries (move to see the list of tables, and move back to see
the list of queries... you should see toto in Access 2002 and 2003) . Even
if it is not listed with Access 2000, the stored proc would work for that
version too.

The Execute in the Connection.Execute is a method that belong to the ADO
connection. The EXECUTE in "EXECUTE toto 222" is a SQL keyword. So, one
execute indicates a method to a connection object, the other, is part of the
SQL Syntax. Just like two dogs can be called toto, that does not mean the
two dogs are just one, or that one of the dog does not need to be there it
the other is... :-) As dog, they are just unaware of the presence of each
other.


Hoping it may help,
Vanderghast, Access MVP



Vanderghast,

You seem to tell me eaxctly what I need, but I have difficulties to digest
it.

1.
myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"

You mean this will create query called toto containing the following SQL:
SELECT MIN(whatever/[argument]) FROM somewhere; ?

I run this line of code and no query was created. Also no error was
generated.
Access does not support stored procedures - does this matter?


2. Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

This syntax confuses me.
Why not
Set myAdoRst = myConnection.Execute( "toto 222 ") ?
Why do I need the second EXECUTE ?



Michel Walsh said:
Hi,


myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"


Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

ie: use the stored proc as if it was a function, but without ( )
Click to expand...
around
the
arguments list.


It works only with Jet 4.0 ( Access 2000 or later), and only with ADO.


The syntax is

CREATE PROC procName ( listOfArguments) AS sqlStatement


You can drop the procedure like this:

DROP PROC procName


You can use the complete word PROCEDURE instead of just PROC. Note that
you can also use an action query, not just a SELECT query, as sql statement.





Hoping it may help,
Vanderghast, Access MPV


<aa> wrote in message Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the
query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


What are you using for data access? DAO or ADO? If you're
Click to expand...
writing
ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO
examples
on
the net...see http://www.jansfreeware.com/articles/adoasp.html or Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the
syntax
to
allow the query to take a parameter for MIN function?


message
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Hi,



The statement is an acceptable one. Note that since there is no group, the
MIN occurs over all the records, so you may have got the same result with:

variable= xnn.Execute("SELECT MIN(whatever) FROM
somewhere;").Fields(0).Value / argument



Hoping it may help,
Vanderghast, Access MVP


Thanks, Michel for detailed reply.
I am using Access2000 and I wanted to add this query to the mdb file on my
local PC and upload it on the server.
For that purpose the statement I guessed
SELECT MIN(whatever/[argument]) FROM somewhere;
- is it correct?

Regarding the rest - I need time to digest it and play with it. Perhaps I'll
return here to morrow

Michel Walsh said:
Hi,


The CREATE PROC would create a stored procedure, which Jet 4.0 accepts
( but they are limited to one sql statement ). The query would be listed in
Access 2002 or in Access 2003, but not in Access 2000, if you refresh the
list of the queries (move to see the list of tables, and move back to see
the list of queries... you should see toto in Access 2002 and 2003) . Even
if it is not listed with Access 2000, the stored proc would work for that
version too.

The Execute in the Connection.Execute is a method that belong to the ADO
connection. The EXECUTE in "EXECUTE toto 222" is a SQL keyword. So, one
execute indicates a method to a connection object, the other, is part of the
SQL Syntax. Just like two dogs can be called toto, that does not mean the
two dogs are just one, or that one of the dog does not need to be there it
the other is... :-) As dog, they are just unaware of the presence of each
other.


Hoping it may help,
Vanderghast, Access MVP



Vanderghast,

You seem to tell me eaxctly what I need, but I have difficulties to digest
it.

1.
myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"

You mean this will create query called toto containing the following SQL:
SELECT MIN(whatever/[argument]) FROM somewhere; ?

I run this line of code and no query was created. Also no error was
generated.
Access does not support stored procedures - does this matter?


2. Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

This syntax confuses me.
Why not
Set myAdoRst = myConnection.Execute( "toto 222 ") ?
Why do I need the second EXECUTE ?



Hi,


myConnection.Execute "CREATE PROC toto( argument LONG) AS SELECT
MIN(whatever/argument) FROM somewhere ;"


Set myAdoRst = myConnection.Execute( " EXECUTE toto 222 ")

ie: use the stored proc as if it was a function, but without ( ) around
the
arguments list.


It works only with Jet 4.0 ( Access 2000 or later), and only
Click to expand...
with
ADO.
The syntax is

CREATE PROC procName ( listOfArguments) AS sqlStatement


You can drop the procedure like this:

DROP PROC procName


You can use the complete word PROCEDURE instead of just PROC. Note
that
you can also use an action query, not just a SELECT query, as sql
statement.





Hoping it may help,
Vanderghast, Access MPV


<aa> wrote in message Thanks, Paul
I use ADO in ASP
I posted this question here in Access NG because I wanted to store the
query
in the .mdb file and then call this query from ASP code passing the
parameter

So what would be the syntax for such stored query?


What are you using for data access? DAO or ADO? If you're writing
ASP,
you're in the wrong NG. Nevertheless, there are hundreds of ADO
examples
on
the net...see http://www.jansfreeware.com/articles/adoasp.html or
Google

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message Thanks, Paul

QueryDefs - is this from VB or from ASP too?
I searched ASP 3.0 documentation and found no such collection

If I already have this query stored in Access, what would be the
syntax
to
allow the query to take a parameter for MIN function?


message
Assuming you already have a database object named db...

Dim qdf
Dim rst

Set qdf = db.QueryDefs("somequery")
qdf.Parameters("someparameter") = yourvariable

Set rst = qdf.OpenRecordset()

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


<aa> wrote in message From another application (ASP) I run a query using
MIN(some_field/parameter)
where parameter is an external value
How can I pass this parameter to the query?
Syntax like
MIN([some_field] / [])
does not work
Click to expand...
Click to expand...
Click to expand...
 
Back
Top