Changing the criterion in a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Am I able to change the criterion in a query under program control?
I would like to use the same query in different circumstances and search by
different criterion based on different fields. Is this possible and, if so,
how would the code structure look?
RayC
 
You do this sort of thing using a form. Enter your criteria into fields on
the form.
You can then enter into the criteria rows of your query.
[Forms]![FormName]![Fieldname]
 
Yes, there are several ways to do this. Which you use will depend on the
circumstances. To address a specific, based on your post, one way is to
create a stored query that has no criteria at all (no WHERE). Then,
programmatically create the WHERE portion of the query. Now, retrieve the
SQL of a "template" version of the query, add the WHERE part to it, and save
it back to the stored query.

Here is an example:

Dim strWhere As String
Dim strSQL As String

strSQL = CurrentDb.QueryDefs("qselLaborWeeklyDetailXLTemplate").SQL
strWhere = BuildWhere()
If Len(strWhere) > 0 Then
strSQL = Replace(strSQL, ";", "Having " & strWhere)
End If
CurrentDb.QueryDefs("qselLaborWeeklyDetailXL").SQL = strSQL

Here is an example of how I build a Where condition string when there are
multiple controls that may or may not be included in the criteria:

Private Function BuildWhere() As String
Dim strWhere As String

On Error GoTo BuildWhere_Error

If Len(Me.cboActivity & "") > 0 Then
strWhere = "[CISAttributeTable_ME]![Activity] = '" & Me.cboActivity
& "'"
End If

If Len(Me.cboAcctgUnit & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[PerformAcctUnit] = '" & Me.cboAcctgUnit & "'"
End If

If Len(Me.cboEmployeeID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = strWhere & "[EmployeeNum] = '" & Me.cboEmployeeID & "'"
End If

BuildWhere = strWhere

BuildWhere_Exit:

On Error Resume Next
Exit Function

BuildWhere_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure BuildWhere of VBA Document
Form_frmLaborWeeklySelection"
GoTo BuildWhere_Exit

End Function
 
One way I did was to have a form with option group and multiple text boxes
for entring criteria.
In the query add a field to the grid for the option group. Have a row for
each selection of option group and criteria to go with it.

OPT 1 - field X > textbox1
OPT 2 - field X < textbox1
OPT 3 - field X between textbox1 and textbox2
OPT 4 - field Y = textbox1
 
Back
Top