BuildCriteria for a multiparameter query

[Dumferling]

I am building a filter for a multi parameter query. Users select from the dropdowns and then the code assembles the criteria. I am not seeing the results that I expect. The code is a re-use from other forms where it has worked effectively. Of the three criteria, one assembles properly, one just doesn't work and the third breaks down the wording into separate words.

The first successful one looks like this:
If Not IsNull(Me.cboLegal) Then
If vblWhere = " WHERE " Then
vblWhere = vblWhere & BuildCriteria("tblAMMBusiness.LegalAdviser", dbText, cboLegal)
Else
vblWhere = vblWhere & " and " & BuildCriteria("tblAMMBusiness.LegalAdviser", dbText, cboLegal)
End If
End If

cboLegal looks like this:

(my name so not worried about personal information)

The second code looks like this:
If Not IsNull(Me.cboStatus) Then
If vblWhere = " WHERE " Then
vblWhere = vblWhere & BuildCriteria("tblRUpdates.BUUpdated", dbText, cboStatus)
Else
vblWhere = vblWhere & " and " & BuildCriteria("tblRUpdates.BUUpdated", dbText, cboStatus)
End If
End If

vboStatus looks like this:

The string simply does not assemble even though the lines are processed.

THe third code looks like this:
If Not IsNull(Me.cboBU) Then
If vblWhere = " WHERE " Then
vblWhere = vblWhere & BuildCriteria("tblRUpdates.BusinessUnit", dbText, cboBU)
Else
vblWhere = vblWhere & " and " & BuildCriteria("tblRUpdates.BusinessUnit", dbText, cboBU)
End If
End If
cboBU looks like this:

When the string assembles into the Where clause it looks like this:
" WHERE tblAMMBusiness.LegalAdviser="David Knight" and tblRUpdates.BusinessUnit="Client Solutions""
The second criteria is missing which suggests it is reading it as a Null. However, I have checked that the code actually executes - it just doesn't assemble the string.

From the third code if I change the criteria it looks like this:

On assembly it looks like this:
" WHERE tblAMMBusiness.LegalAdviser="David Knight" and tblRUpdates.BusinessUnit="Service" And tblRUpdates.BusinessUnit="Operations""

The second criteria is still missing and it has now split the phrase into 2 parts. I assume that it is somehow reading the "and" and creating two terms.

I am mystified by the lack of assembly of the second criteria and cannot find any tips on how to hand the break up in the third code block if I have an "and" in the criteria. Any advice would be most welcome!

 

[cheekybuddha]

Change your tests for vblWhere to:

If Left(vblWhere, 6) = " WHERE" Then
' ...'

 

[Minty]

You'll need to use single quotes around any of the strings you are passing in meaning that this:
" WHERE tblAMMBusiness.LegalAdviser="David Knight" and tblRUpdates.BusinessUnit="Service" And tblRUpdates.BusinessUnit="Operations""

Should look like this:
" WHERE tblAMMBusiness.LegalAdviser='David Knight' and tblRUpdates.BusinessUnit='Service' And tblRUpdates.BusinessUnit='Operations'"

Get used to using a Debug.Print vblWhere to see what it creates in the immediate window.

 

[arnelgp]

just assemble them without testing whether there is already a "where".
then at the end you remove the last "And".
also, do it manually:

If Not IsNull(Me.cboLegal) Then
    vblWhere = vblWhere & "tblAMMBusiness.LegalAdviser = '" & Me.cboLegal & "' And "
End If
If Not IsNull(Me.cboStatus) Then
    vblWhere = vblWhere & "tblRUpdates.BUUpdated = '" & Me.cboStatus & "' And "
End If
If Not IsNull(Me.cboBU) Then
    vblWhere = vblWhere & "tblRUpdates.BusinessUnit = '" & Me.cboBU & "' And "
End If
If Len(vblWhere) <> 0 Then
    vblWhere = " Where " & Left$(vblWhere, Len(vblWhere) - 5)
Else
    vblWhere = " Where (1=1)"
End If

 

[Josef P.]

BuildCritieria is good, but sometimes too overzealous.

Debug.Print BuildCriteria("BusinessUnit", dbText, "Service and Operations")

=> BusinessUnit="Service" And BusinessUnit="Operations"
Remedy: mark the contiguous text:

Debug.Print BuildCriteria("BusinessUnit", dbText, "'Service and Operations'")
=>
vblWhere = vblWhere & " and " & BuildCriteria("tblRUpdates.BusinessUnit", dbText, "'" & Replace(cboBU, "'", "''") & "'")

Tip for joining the strings:

If Not IsNull(Me.cboLegal) Then
    vblWhere = vblWhere & " and " & BuildCriteria("tblAMMBusiness.LegalAdviser", dbText, cboLegal)
end if
If Not IsNull(Me.cboStatus) Then
    vblWhere = vblWhere & " and " & BuildCriteria("tblRUpdates.BUUpdated", dbText, cboStatus)
    ' BUUpdated is a text data field?
end if
If Not IsNull(Me.cboBU) Then
     vblWhere = vblWhere & " and " & BuildCriteria("tblRUpdates.BusinessUnit", dbText, "'" & Replace(cboBU, "'", "''") & "'")
End If
if len(vblWhere) > 0 then
      vblWhere = " where " & mid(vblWhere, len(" and ") + 1)
end if

[a little OT]
In my applications, it would look like this (FilterStringBuilder, SqlTools, StringCollection):

With New FilterStringBuilder
   .Add "tblAMMBusiness.LegalAdviser", SQL_Text, SQL_Equal, Me.cboLegal.Value
   .Add "tblRUpdates.BUUpdated", SQL_Text, SQL_Equal, Me.cboStatus.Value
   .Add "tblRUpdates.BusinessUnit", SQL_Text, SQL_Equal, Me.cboBU.Value
   vblWhere = .ToString()
End With

If Len(vblWhere) > 0 Then
   vblWhere = " where " & vblWhere
End If

 

[cheekybuddha]

You'll need to use single quotes around any of the strings you are passing

BuildCriteria handles this.

 

[Minty]

BuildCriteria handles this.

I missed the use of that in the title Thought it was a typo....

 

[Josef P.]

BuildCriteria handles this.

But not with this text: "Service and Operations" (see #5)

Expected result: DataField = 'Service and Operations' or DataField = "Service and Operations"
Outcome: BusinessUnit="Service" And BusinessUnit="Operations" ... this condition will always be false.

 

[cheekybuddha]

But not with this text: "Service and Operations" (see #5)

I don't know anyone who uses BuildCriteria (well, except Dumferling ), but for your use case you must add quotes:

?Application.BuildCriteria("DataField", dbText, "'Service and Operations'")
DataField='Service and Operations'

' or

?Application.BuildCriteria("DataField", dbText, """Service and Operations""")
DataField="Service and Operations"

 

[MajP]

I built a set of procedures way more powerful and flexible than BuildCriteria, so you may be interested in this code. I built this years ago after getting tired of doing this, and I can

• build a filter from any control (combos, listboxes, textboxes, find as you type, options groups, two text boxes [between]) with a single function call. Normally only need to provide the control name.
• Handle multiselect listboxes
• Handle all data type delimiters (#,1,Null).
• Return any type of filter (like*, *like*, =, <, >, between, not, in(,,,) etc.)
• Combine any number of filters

In most cases I can simply use the function
getFilterFromControl(controlobject)
and it will build a proper filter for that field. It does this by determining the field to use and its datatype. There are sometimes exception to this where you cannot determine the field to use and the datatype and there are additional parameters to handle it.

There are exceptions and additional parameters are available to modify the filter if not using the bound column.
1. If the bound column of the combo or listbox is not the column you want to create the filter, you can specify another column.
2. If the column that you want to build the filter is not the name of the field to filter. Example you have UserID in the multiselect listbox but you want to filter in another table using UserID_FK. You can specify this name and it will build
UserID_FK in (22,34,56).
3. With a textbox or option group you have to specify the field to filter since there is no way to derive that.
4. If the datatype in control is not the same data type for the filter. Example the field in the control is a date but your filter needs to convert it to a string.

Because this is so flexible the demo shows multiple ways to use these additional parameters.

I then have a filter combiner that combines multiple filters easily. This code saves me hours.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Josef P.]

I don't know anyone who uses BuildCriteria (well, except Dumferling ),

I use BuildCriteria ... but my own.

but for your use case you must add quotes:

see #5

Note: People like to forget to double the ' when users are allowed to enter text in a textbox. (SQL injection!)

CriteriaString = "DataField = '" & Replace(TextBoxWithUserInput.Value, "'", "''") & "'"

Since you don't always want to write Replace, I can only recommend using something similar to BuildCriteria or at least a function to convert a value to SQL text.
e.g.:

CriteriaString = "DataField = " & SqlTools.TextToSqlText(TextBoxWithUserInput.Value)

 

[cheekybuddha]

Something like:

Function SQLStr(vIn As Variant, _
                Optional blUseNull As Boolean, _
                Optional blWrap As Boolean = True, _
                Optional delim As String = "'") As String

  Dim ret As String

  If Not IsNull(vIn) Then
    ret = Replace(CStr(vIn), delim, delim & delim)
  Else
    If blUseNull Then
      ret = "NULL"
      blWrap = False
    End If
  End If
  If blWRap Then ret = delim & ret & delim
  SQLStr = ret

End Function

?

 

[MajP]

I provide the CSql to handle this case and others in that demo.

 

[cheekybuddha]

I know.