Create a Dynamic generated SQL query in VB/VBA 
Author Message
 Create a Dynamic generated SQL query in VB/VBA
I am working on creating a dynamic generated SQL query in VBA Access based on the search criteria entered into a subform's controls.  There are 7 fields to search by and an option box that determines which table to search in.
I am working on a separate function for the SELECT, WHERE, and ORDER BY clauses, Then concatenate each functions string into one SQL string.
If you could provide some pointers or ideas of how to write something like this efficiently, it would be appreciated.

Thanks,

--
MWS



Sat, 01 May 2004 08:58:26 GMT
 Create a Dynamic generated SQL query in VB/VBA

Dear MWS:

When you generate the WHERE clause (that's the part your questions about, right?) you begin by testing one of the potential criteria (I'm thinking you must check each one to see whether the user has chosen to make a selection or
not).  Start with an empty string, of course.  If you need to add to a criteria for that selection you add "WHERE " if the string is empty and "AND " if it is not.  Then put in "FieldName = " & Control.Value.  You'll need to put
single or double quotes around the control value whenever the field is text.

Make sure you know exactly how the query should read and see that the code does this exactly, step by step.  Before long you'll be really good at it.

If any of your criteria require OR between them as well as AND then you'd better be ready to formally analyse the logic of it.  If you can't write the query manually just looking at the selections on the screen then you're not
ready to code it!

Tom Ellison

Quote:

> I am working on creating a dynamic generated SQL query in VBA Access based on the search criteria entered into a subform's controls.  There are 7 fields to search by and an option box that determines which table to search in.
> I am working on a separate function for the SELECT, WHERE, and ORDER BY clauses, Then concatenate each functions string into one SQL string.
> If you could provide some pointers or ideas of how to write something like this efficiently, it would be appreciated.

> Thanks,

> --
> MWS



Sat, 01 May 2004 10:35:45 GMT
 Create a Dynamic generated SQL query in VB/VBA
Other concerns:
Will all fields always be added to the where clause? - i.e.  If a user does
not enter any data, do you not use the field, or do a NULL search -  select
* where name is NULL
I wrote an app where I placed check boxes above the field text boxes.  If
the check box was selected a text box is displayed.  The user can then enter
the search data or leave the field blank to do an IS NULL search.
In the click event of check box:  txtName.visible = (chkName.value =
vbChecked)
Another thought - allow user to specify qualifiers on Number Fields: =, < or
Quote:
> via a drop down list.



Quote:
> Dear MWS:

> When you generate the WHERE clause (that's the part your questions about,

right?) you begin by testing one of the potential criteria (I'm thinking you
must check each one to see whether the user has chosen to make a selection
or
Quote:
> not).  Start with an empty string, of course.  If you need to add to a

criteria for that selection you add "WHERE " if the string is empty and "AND
" if it is not.  Then put in "FieldName = " & Control.Value.  You'll need to
put
Quote:
> single or double quotes around the control value whenever the field is
text.

> Make sure you know exactly how the query should read and see that the code

does this exactly, step by step.  Before long you'll be really good at it.
Quote:

> If any of your criteria require OR between them as well as AND then you'd

better be ready to formally analyse the logic of it.  If you can't write the
query manually just looking at the selections on the screen then you're not
Quote:
> ready to code it!

> Tom Ellison


> > I am working on creating a dynamic generated SQL query in VBA Access

based on the search criteria entered into a subform's controls.  There are 7
fields to search by and an option box that determines which table to search
in.
Quote:
> > I am working on a separate function for the SELECT, WHERE, and ORDER BY

clauses, Then concatenate each functions string into one SQL string.
Quote:
> > If you could provide some pointers or ideas of how to write something

like this efficiently, it would be appreciated.
Quote:

> > Thanks,

> > --
> > MWS



Sat, 08 May 2004 11:32:14 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. dynamic type create (was return setof record from function with dynamic query)

2. Generate SQL Queries to create destination tables in Transformation Data Task

3. Problem creating query strings using dynamic SQL...

4. Nationwide-239587--ORACLE-Developer 2000-VBA-Visual Basic-Java-Oracle, VB/VBA, Java Developer

5. Making a Dynamic Web Page From an Application Created in VB 5.0

6. How to Create a query that generate a sequence number as a column

7. SQL generated inside dynamic W4GL frames: problem and workaround

8. cursor ref and generating dynamic SQL

9. 3rd party generated Dynamic SQL vs Packages


 
Powered by phpBB® Forum Software