Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to dynamically build a query from controls on a form
Parameter Reports In Code
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.
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.
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!
Finally we will tack the main body of the SQL to the WHERE clause, and we are in business!
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:
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.
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 & "'";
EndifSo 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") & "#"
EndifFinally 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 FunctionFinally 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") & "#&"
EndifStep 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.