INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Parameter Reports In Code

How to dynamically build a query from controls on a form by mincefish
Posted: 16 Aug 02 (Edited 19 Aug 04)

I have answered this question twice, and someone suggested I write an FAQ on it, so here we are

This assumes that the user is using Access 97, with DAO the prevalent reference.

Control-wise we need to assume, in this example, that we have a form, which contains 4 controls:
    - 1 * Combo Box, holding a list of divisions, called cboDivision. These values relate to a field called Division
    - 1 * Combo Box, holding a list of regions called cboRegion. These values relate to a field called Region
    - 2 * Text Box, called txtStart and txtEnd. These will have, suprisingly, a start and end date entered in them, which will correspond to an AssignDate

None, some or all of the controls might be populated when the user clicks on run.

So the fuction will look like this - I'll interrupt the code to put some more lengthy comments on what I'm doing.
There are two schools of thought about how to do this - firstly I'll show you the way I've always done it.

CODE

Private Function fncGenerateQuery() As Boolean
Dim strSQL As String
Dim qdfTemp As QueryDef
Dim strWhere as String 'Dim'd a new var to hold the where clauses

strSQL = "SELECT * FROM visitors ";

So I've dimmed strSQL for the main body of my SQL (i.e. SELECT & FROM), and strWhere for the WHERE part of the SQL, which we are going to dynamically build.
Lastly, I've dim'd a qdfTemp, a querydef. This is because we want to write this SQL into a query, so that reports can be run off it. Its pretty incedental to this, but I thought I'd include the complete package.

CODE

If not (Me.cboDivision.Value = vbNullString) then
    If len(strWhere) > 0 then
        strWhere = strWhere & " WHERE "
    Else
        strWhere = strWhere & " AND "
    Endif

    strWhere = strWhere & " Division = '" & Me.cboDivision.Value & "'";
Endif

So Basically what I've done there is firstly checked if there's anything in the combo box...no point carrying on if there's not!

The I check to see if anything's already been added to the WHERE part of SQL String. I know this is the first one, so nothing will have been added, but this kinda allows for easier maintenance if you were to add one check in before, or something. If I find that nothings been added, I know that I need to put a WHERE infront of the criteria. If there is something in that string, then we know that at least one set of criteria has been added, so we no longer need to add a WHERE - now we need to add an AND...and the rest of the code is pretty similar!

CODE

If not (Me.cboRegion.Value = vbNullString) then
    If len(strWhere) > 0 then
        strWhere = strWhere & " WHERE "
    Else
        strWhere = strWhere & " AND "
    Endif

    strWhere = strWhere & " Region = '" & Me.cboRegion.Value & "'";
Endif

If (not (isnull(me.txtStart.Value))) And (not (isnull(me.txtEnd.Value))) then
    If len(strWhere) > 0 then
        strWhere = strWhere & " WHERE "
    Else
        strWhere = strWhere & " AND "
    Endif

    strWhere = strWhere & " AssignDate Between #" & Format(me.txtStart.Value,"MM/DD/YY") & "# AND #" & Format(me.txtEnd.Value, "MM/DD/YY") & "#"

Endif

Finally we will tack the main body of the SQL to the WHERE clause, and we are in business!

CODE

strSQL = strSQL & strWhere

Set qdfTemp = CurrentDb.QueryDefs("qryMain")
qdfTemp.SQL = strSQL
Set qdfTemp = Nothing
    
fncGenerateQuery = True
    
End Function

Finally we open a query def on a prebuilt query called qryMain, as rptMain is running off that query.
We modify its .SQL property, and then set it back to nothing. Robert will truly be your fathers brother.

There's another school of thought, that seems quite cheeky, although its something I've never tried.

It is arguable quite a longwinded process checking whether we need to add a WHERE or an AND, so it was suggested to me that the following was done:

CODE

strSQL = "SELECT * FROM visitors WHERE 1 = 1"

If not (Me.cboDivision.Value = vbNullString) then

    strSQL= strSQL & " AND Division = '" & Me.cboDivision.Value & "'"
Endif

If not (Me.cboRegion.Value = vbNullString) then
    strSQL = strSQL & ";  AND Region = '" & Me.cboRegion.Value & "'";
Endif

If (not (isnull(me.txtStart.Value))) And (not (isnull(me.txtEnd.Value))) then
    strSQL  = strSQL &  " AND AssignDate Between #" & Format(me.txtStart.Value,"MM/DD/YY") & "# AND #" & Format(me.txtEnd.Value, "MM/DD/YY") & "#&"

Endif

Step up and take your pick folks. I like them both, although secretly I suspect I shall continue to use the first way. Why? Because at times I can be a change fascist!

Hope this helps.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close