Add WHERE condition to Query without copying entire SQL

[leicklda]

Not sure if I'm missing some big concept here...

I have a pretty big query that is used as a rowsource for a listbox.

I want to filter this listbox/query in lots of different ways based on
different buttons I press from around the screen. I know that I can
copy the sql from this query into a listbox.rowsource line in VB, and
add the WHERE condition to the end of the SQL code for each different
OnClick event procedure.

The problem is that this is a pretty hefty SQL statement, and once I
copy it into each procedure, it is then very tedious to modify if I
need to make a change (which I often need to do because this whole
project is constantly evolving) because of a) the size of it, and b)
the fact that it is redundantly written into so many different
procedures.

I would love to just have one same query that I can call from each
different procedure, and then ADD a where statement onto each
procedure's query reference, without have to "explode" the query into
SQL for each reference to it.

Is there some way I can do this? Some filter method I'm not aware of?

Thanks!

 

[leicklda]

OK I figured out how to do this - I wrote out the SQL and assigned it as MasterSrc. I made another variable FilterA and set it to define my HAVING statement.

For one of the OnClick procedures, I wrote
listbox.rowsource = MasterSrc & FilterA

This works. But now how would I make it so that MasterSrc is something I can define only once and have various OnClick procedures be able to reference?

Thanks!

 

[BillPower]

Declare MasterSrc (Dim MasterSrc as String)in the Declarations section of your form, after Option Compare Database, Option Explicit.

In the On Load Event of your form,

MasterSrc = YourSQL

You will now be able to call MasterSrc from any event on you Form.

 

[SBendBuckeye]

Taking Bill's suggestion above I would then incorporate the whole process into a function. I believe you should be using Where clauses instead of Having clauses because the latter works with Group By statements whereas the former can stand alone.

I would break the process into 2 or 3 pieces as below:

1. In the Declarations area at the top of your module,
declare a module level variable to hold your SQL:

Private mstrSQL As String

2. Get the SQL for any TableDef or QueryDef you are using:
a. QueryDef

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName&quot
mstrSQL = qdf.SQL
Set qdf = Nothing

b. TableDef

Dim qdf As QueryDef
Dim strSQL As String
strSQL = "Select * From YourTableName"
Set qdf = CurrentDb.CreateQueryDef(vbNullString, strSQL)
mstrSQL = qdf.SQL
Set qdf = Nothing

If you don't pass a name to the CreateQueryDef method it will create a temporary QueryDef for you which is all we need for our purposes here.

c. Your own SQL string

Dim qdf As QueryDef
Dim strSQL As String
strSQL = YourOwnSQLString
Set qdf = CurrentDb.CreateQueryDef(vbNullString, strSQL)
mstrSQL = qdf.SQL
Set qdf = Nothing

The above will do basic SQL validation for you (eg you fat finger Select as Selct). If will not verify JOINs, incorrectly spelled fields, etc.

3. Create a public function in a standard module to create or add a Where clause to an SQL string passed into it:

Public Function AddWhereClause(pstrSQL As String, _
pstrNewCriteria As String) As String
Dim strTemp As String
Dim strTheRest As String
Dim strBefore As String
Dim strWhere As String
Dim lngPos As Long
'SQL order Select, From, Where, GroupBy, Having, OrderBy
strTemp = UCase$(pstrSQL)
lngPos = InStr(strTemp, "GROUP BY&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
'Don't think you can have Having without Group By
lngPos = InStr(strTemp, "HAVING&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
lngPos = InStr(strTemp, "ORDER BY&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
strBefore = strTemp
End If
End If
End If
'At this point everything after a Where clause is now
'in strTheRest and everything else is in strBefore - At
'this point we don't know if we have a Where clause
lngPos = InStr(strBefore, "WHERE&quot
If lngPos > 0 Then
strWhere = Mid$(strBefore, lngPos)
strBefore = Left$(strBefore, lngPos - 1)
End If
If Len(strWhere) > 0 Then 'Add new criteria with AND
strWhere = strWhere & " And " & pstrNewCriteria
Else
strWhere = " Where " & pstrNewCriteria
End If
strWhere = strWhere & " " 'I always abutt a space
'Now that we have all the pieces just return them
AddWhereClause = strBefore & strWhere & strTheRest
End Function

You would invoke the function like this assuming that your
valid criteria might be "MasterID = " & FormControlName or any other Where clause string without the Where portion:

strMySQL = AddWhereClause(strMySQL, "ValidCritera&quot

This technique can be used to build Query SQL on the fly based on user selected values out of text boxes, combo boxes or list boxes. In your form's After Update processing just call the above function with any that the user has changed.

Good Luck!

 

[SBendBuckeye]

Sorry, the above function assumed someone was parsing an entire SQL string complete with Select, From, etc. As such, it would not work if all you were trying to do was build a criteria or filter condition to use with DoCmd.OpenForm, etc where you build a Where clause without the Where.

Here is an amended version that will properly handle partial SQL statements as well. Just replace the function in number 3. above with these functions.

Sorry for the inconvenience!

If you are working with complete SQL you would still invoke the function as above:

strMySQL = AddWhereClause(strMySQL, "ValidCritera&quot

If you are building only a criteria or filter string you could invoke it via AddWhereClause or with AddCriteriClause:

strCrit = AddWhereClause(strCrit, "ValidCritera", True)
strCrit = AddCriteriaClause(strCrit, "ValidCritera&quot

Public Function AddWhereClause(pstrSQL As String, _
pstrNewCriteria As String, _
Optional pblnPartialSQL As Boolean) As String
Dim strTemp As String
Dim strTheRest As String
Dim strBefore As String
Dim strWhere As String
Dim lngPos As Long
If pblnPartialSQL Then
AddWhereClause = _
AddCriteriaClause(pstrSQL, pstrNewCriteria)
Exit Function
End If
'SQL order Select, From, Where, GroupBy, Having, OrderBy
strTemp = UCase$(pstrSQL)
lngPos = InStr(strTemp, "GROUP BY&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
'Don't think you can have Having without Group By
lngPos = InStr(strTemp, "HAVING&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
lngPos = InStr(strTemp, "ORDER BY&quot
If lngPos > 0 Then
strTheRest = Mid$(strTemp, lngPos)
strBefore = Left$(strTemp, lngPos - 1)
Else
strBefore = strTemp
End If
End If
End If
'At this point everything after a Where clause is now
'in strTheRest and everything else is in strBefore - At
'this point we don't know if we have a Where clause
lngPos = InStr(strBefore, "WHERE&quot
If lngPos > 0 Then
strWhere = Mid$(strBefore, lngPos)
strBefore = Left$(strBefore, lngPos - 1)
End If
If Len(strWhere) > 0 Then 'Add new criteria with AND
strWhere = strWhere & " And " & pstrNewCriteria
Else
strWhere = " Where " & pstrNewCriteria
End If
strWhere = strWhere & " " 'I always abutt a space
'Now that we have all the pieces just return them
AddWhereClause = strBefore & strWhere & strTheRest
End Function 'AddWhereClause

Public Function AddCriteriaClause(pstrCriteria As String, _
pstrNewCriteria As String) As String
Dim strTemp As String
If Len(pstrCriteria) = 0 Then
strTemp = pstrNewCriteria
Else
strTemp = pstrCriteria & " And " & pstrNewCriteria
End If
AddCriteriaClause = strTemp
End Function 'AddCriteriaClause

Hope this helps and Good Luck!