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.
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.
If not (Me.cboDivision.Value = vbNullString) then If len(strWhere) > 0 then strWhere = strWhere & " WHERE " Else strWhere = strWhere & " AND " 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!
If not (Me.cboRegion.Value = vbNullString) then If len(strWhere) > 0 then strWhere = strWhere & " WHERE " Else strWhere = strWhere & " AND " 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") & "#"
Finally we will tack the main body of the SQL to the WHERE clause, and we are in business!
strSQL = strSQL & strWhere
Set qdfTemp = CurrentDb.QueryDefs("qryMain") qdfTemp.SQL = strSQL Set qdfTemp = Nothing
fncGenerateQuery = True
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:
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") & "#&"
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!