Dear all,

I have a query for my database which I would like to adapt for tables 
within the database. Rather than make multiple copies of the query, is it 
possible to use a combo box in a form to allow the user to select which 
databases they want to query and have this entered into the SELECT, FROM  
and WHERE command ? 

For example, can you say SELECT all fields in database X and database Y 
FROM database X and Y, WHERE [X_field1]=[Y_field1]

Thus the query only returns matching values from the two databases that 
the user selected.

Was thinking it might be easier to use a single table, but still need to 
use the combo box to enter the variables for the query.

Any comments appreciated.

Cheers

James

It is possible to write VB code that will construct SQL statements and
execute them.  Most of my apps do this, and at another company we built a
system to run survey projects, where each project had a data structure that
was defined by the users as each project came through the door - the app
took their input and generated "CREATE TABLE" statements and as the project
ran it generated all "SELECTS" and everything on the fly.  So it can be done
and be reliable and efficient.

The trick is getting valid input from the users.  If you let users specify
multiple tables to join, they need limits.  Can they join employee name to
customer street address?  They are both text fields so why not?  Because
they don't MEAN the same thing.  If you predefine the joining fields and
only let users select which tables (let the code know how to join them) that
avoids some trouble.

using the data input by the users you construct the SQL statements you need
and execute them using adoConnection.Exec or however you prefer to do that
(DAO , JET etc)



-----Original Message-----
From: James [mailto:j.boorman@u...]
Sent: Tuesday, August 27, 2002 6:37 AM
To: Access
Subject: [access] User input to determine WHERE statement


Dear all,

I have a query for my database which I would like to adapt for tables 
within the database. Rather than make multiple copies of the query, is it 
possible to use a combo box in a form to allow the user to select which 
databases they want to query and have this entered into the SELECT, FROM  
and WHERE command ? 

For example, can you say SELECT all fields in database X and database Y 
FROM database X and Y, WHERE [X_field1]=[Y_field1]

Thus the query only returns matching values from the two databases that 
the user selected.

Was thinking it might be easier to use a single table, but still need to 
use the combo box to enter the variables for the query.

Any comments appreciated.

Cheers

James