Using a query/SQL statement as a function 
Author Message
 Using a query/SQL statement as a function

Could someone point me in the right direction (Or just tell me :)) of how to
use some form of a query (Access query, SQL statement, etc.) to act like a
function and return a value?

I have two things that I need to do and I think they both can be
accomplished through a value returned from a query.

The first is I have a product table.  In the product table I have a
manufacturer's ID number, and a vendor's ID number.  I have a companies
table that I keep all the manufacturers and vendors in.  This companies
table has a web address hyperlink value.  On a form, I'd like to have a
hyperlink using both the manufacturer's and vendor's web addresses.  I get
the manufacturer's and vendor's names to come up using a combo box.  This
works for the web address to, but you can't click on it.  A query can say
here's the hyperlink for company ID 1234, now I just need to know how to say
MyTextBox = ThatQuery

The second is that I create my own ID number from four pieces of information
in the products table.  Each product has a general category, specific
category, manufacturer, and sequential number.  The first three are defined,
the sequential number is calculated.  The sequential number is 0 unless
there are more than one products in the same general and specific categories
from the same manufacturer.  If that is the case, the sequential number is
bumped up for each additional product.  A query can give you the count that
matches general and specifgic categories and manufacturers, I just need to
know how to say SeqNum = ThatQuery

I looked through the DoCmd, and tried expressions, but to know avail.

Thanks for any and all help.


Mon, 31 Dec 2001 03:00:00 GMT  
 Using a query/SQL statement as a function

Have a look at DLookup (and possibly related functions like DCount, DSum
etc - they're collectively called the Domain Aggregate functions) in help.

Simon Lewis

Mon, 31 Dec 2001 03:00:00 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Sql query statement (using a variable)

2. Unable to modify SQL statement using string functions

3. SQL-statement using Date-function?

4. Using Functions in SQL Statement

5. Using a Function within an SQL-Statement

6. Using a vba function in an SQL query?

7. SQL query: from SELECT statement to stored procedure with dynamic PL/SQL

8. function used in query repeats for each query value

9. Using Sub Query in Sql Query - Crystal Report 5.0

10. Get SQL Statement from Access queries

11. Extract SQL statements of queries

12. reformatting of SQL statements in queries


Powered by phpBB® Forum Software