Get Filter from Controls, CSQL, and Combine Multiple Filters

[MajP]

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 a lot of work up front, but in the long run it makes building applications so much easier.
Probably the biggest area I see people struggle and often painfully, is writing proper SQL to use in filters or other SQL code. The biggest issue is how to properly delimit literal values and pulling this information from a control.

The following discussion are all the things you normally have to do to get the SQL string correct. The provided code does all of this for you.

Strings: Strings used in SQL must be wrapped in quotes. If that string contains a single quote inside the string, it needs to be replaced by 2 single quotes. Ex
“…WHERE Some Field = ‘Dog’…”
If the original value is O’Brien it needs to get converted to
“…WHERE SomeField = ‘O’’Brien’…”
You also want to make sure to trim the values so you do not get mistakenly
“…WHERE SomeField = ‘ Dog ’…”
The CSQL will do this for you.

Dates: Dates give most people the biggest issue. Dates HAVE TO BE IN US FORMAT MM/DD/YYYY or some other non ambiguous format and surrounded by number signs. Regardless of any regional format you have, it still requires this format.
“…WHERE SomeField = #05/19/2020# …”
If it has a time component
“…WHERE SomeField = #5/19/2020 9:53:04 AM# …”

So writing this out can get long and confusing. Ex:
“…. WHERE SomeField = #” & Format(someDate,”MM/DD/YYYY”) & “#”

Booleans: Booleans can be forgiving depending on how called, but to ensure it works the best is
“…WHERE SomeField = -1 …” true
“…WHERE SomeField = 0…” false

Numbers: Do not get delimited
“…WHERE SomeField = 123.45 …”

Nulls: Null values need to get converted to the word NULL
"... Where SomeField IS NULL"
"INSERT into SomeTable (Field1, Field2) values (123, NULL).. "

So I recommend to save a lot of headaches to put this function in your library because it does this formatting for you. Use it like other conversion functions (Cdate, Clng, Ccur)
CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType)

Public Enum SQL_DataType
  sdt_boundfield = -1
  sdt_UseSubType = 0
  sdt_text = 1
  sdt_Numeric = 2
  sdt_date = 3
  sdt_Boolean = 4
  sdt_Null = 5
End Enum

'**************************************************************************************************************
'----------------------------------------Convert To Delimited SQL When Datatype can be Determined -------------
'**************************************************************************************************************


Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String

    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case VarType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If

        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = Str(Value)
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If

End Function

At the top are some optional enumerated constants to force the data type. This may or may not be necessary.
If you pass in a bound control value it can determine the data type. If you pass in a typed variable it will know. If you pass in a variant but it is subtyped it will know. In these cases no need to specificy the Sql_DataType. However there may be no way to know so you have to specify. For example you have an unbound text box and you type in a date. You need to pass in the Sql_Type. Or if you want to convert from one to another. Assume your date is a string but you want to use it as a date. Examples:

Public Sub TestCSql()
  Dim blnVal As Boolean
  Dim dblVal As Double
  Dim lngVal As Long
  Dim dtVal As Date
  Dim dtTmVal As Date
  Dim txtVal As String
  Dim unkValdate As Variant
  Dim unkValStr As Variant
  Dim unkVal As Variant

  blnVal = True
  dblVal = 2.02
  lngVal = 7
  dtVal = Date
  dtTmVal = Now
  txtVal = "Dog"
  unkValdate = Now + 7
  unkValStr = "Cat"
  unkVal = Null

  'Using the known data type or sub type
  Debug.Print CSql(blnVal)
  Debug.Print CSql(dblVal)
  Debug.Print CSql(lngVal)
  Debug.Print CSql(dtVal)
  Debug.Print CSql(dtTmVal)
  Debug.Print CSql(txtVal)
  Debug.Print CSql(unkValdate)
  Debug.Print CSql(unkValStr)
  Debug.Print CSql(unkVal)
  Debug.Print CSql(" O'Brien ")

  'Using defined data types
  Debug.Print vbCrLf & " Using Data types"
  Debug.Print CSql(Format(Now), sdt_date)
  Debug.Print CSql("O'Brien")
  Debug.Print CSql(1.23, sdt_Numeric)
  Debug.Print CSql("1.23", sdt_Numeric)
  Debug.Print CSql(1.23, sdt_text)
  Debug.Print CSql("No", sdt_Boolean)
  Debug.Print CSql(True, sdt_Boolean)
  Debug.Print CSql(Null, sdt_date)
  Debug.Print CSql(Now, sdt_text)
  Debug.Print CSql("1/1/2020", sdt_date)
End Sub

Results

-1
2.02
7
#05/19/2020#
#05/19/2020 10:27:46#
'Dog'
#05/26/2020 10:27:46#
'Cat'
Null
'O''Brien'

Using Data types
#05/19/2020 10:27:46#
'O''Brien'
1.23
1.23
'1.23'
0
-1
Null
'5/19/2020 10:27:46 AM'
#01/01/2020#

Note that O'Brien changed to O''Brien
Note that if you create a variant but assign it a date, string, or number it knows the subtype.
Note how it converts date strings to date format or vice versa when specified using the SQL_Datatype

So to use this you can replace
“…. WHERE SomeField = #” & Format(someDate,”MM/DD/YYYY”) & “#”
with
“…. WHERE SomeField = " & csql(SomeDate)
or
“…. WHERE SomeField = #” & Format(someDate,”MM/DD/YYYY”) & “# AND SomeOtherField = '” & SomeTextField & "'"
“…. WHERE SomeField = ” & csql(someDate) & “ AND SomeOtherField = ” & csql(SomeTextField)

 

[MajP]

Part 2: Pulling values from controls
Personally I never ever have a query reference a control (ex: "...Where SomeField = '" & forms!SomeForm!SomeControl & "'"). I understand people do this all the time. I find it difficult to write, impossible to debug, inflexible, and not reuseable. IMO there are so much simpler and easier ways.

To get a filter from a control you need to first know what type of filter (>, =, like '*..,etc) and the Sql Data type so you can properly delimit it. I answered the second part already.

Use the function GetSQL_Filter. The enumeration is provided

Public Enum FilterType
 flt_Equal = 0
 flt_LikeFromBeginning = 1 'Text*
 flt_LikeAnywhere = 2 '*test*
 flt_LikeFromEnd = 3 '*Text'
 flt_GreaterThan = 4
 flt_GreaterThanOrEqual = 5
 flt_LessThan = 6
 flt_LessThanOrEqual = 7
 flt_Between = 8
End Enum

test

Public Sub TestFilter()
  Dim str As String
  Dim dt As Date
  Dim dt2 As Date
  str = "Dog"
  str = CSql(str)
  Debug.Print GetSQL_Filter(flt_Equal, str)
  Debug.Print GetSQL_Filter(flt_GreaterThanOrEqual, str)
  Debug.Print GetSQL_Filter(flt_LikeAnywhere, str)
  Debug.Print GetSQL_Filter(flt_LikeFromBeginning, str)
  dt = Date
  dt2 = Date + 7
  Debug.Print GetSQL_Filter(flt_Equal, CSql(dt))
  Debug.Print GetSQL_Filter(flt_Between, CSql(dt), CSql(dt2))
End Sub

Results

 = 'Dog'
 >= 'Dog'
Like '*Dog*'
Like '*Dog'
 = #05/19/2020#
BETWEEN #05/19/2020# AND #05/26/2020#

So to pull the filter from the control you need to know the filter type and the sql data type and the field to filter. And this can be radically simplified.

For an unbound textbox you do not know the field to filter and do not specifically know the data type.
The method is mdlControlFilters.GetFilterFromTextBox(TheTextBox,the Sql_datatype, the field name, the filter type, use not)
dim fltr as string
flt = mdlControlFilters.GetFilterFromTextBox(me.TxtBox1,sdt_Text, "field3", flt_LikeAnywhere, True)
if the value of dog is in txtbox1 it produces the string
"Field3 not like '*Dog*'"

However there are potential additional advantages when pulling from a listbox or combobox. You have the option to specify which column to filter, what is the datatype, but this may not be needed. If you plan to use the bound column then no need to specify the column. If you know the column you know the datatype. The function for a combobox or single select list box is.
Public Function GetFilterFromSingleListOrCombo(ctrl As Access.Control, Optional TheFilterType As FilterType = flt_Equal, _
Optional TheSQL_DataType As SQL_DataType = sdt_boundfield, Optional TheColumn As Integer = -1, Optional FieldName As String = "UseBound", _
Optional NotCondition As Boolean = False) As String

str = GetFilterFromSingleListOrCOmbo(me.cmbo)
if the bound column is "Full_Name" and that is the field name I want then it produces
Full_Name = 'Adam Graham'
If it is not the bound column you have to specificy the column
str = GetFilterFromSingleListOrCOmbo(me.cmbo,,,1)
If the field name for the filter is different specify the field name
If you want a NOT condition specify true
If you need to change the sql data type you can specify. So if the column has numbers but you want a string then specify the sql data type.

In the demo there is the code demonstrating pulling the filters from multiple controls.

Multi Select Listbox: Will explain later see demo
Option Group: Will Explain later see demo

Part 3: Combining Multiple filters
(I will explain this in the next post, but you can look at the demo)
To determine the available functions use intellisense. Type in the module name followed by a period.

NOTE: DO NOT worry about looking at my code. If you think it is complicated that is pointless. Focus on using the code and not how it works. Write code once and use it everywhere.

The form DemoCapes demonstrates the flexibility for creating filters based on different controls. The remaining forms show how to combine these filters.

FrmSearch has 7 controls to include a multiselect listbox and a between filter and the ability to make it an And or OR filter. I will let you decide if you would prefer to do that or do the query I showed. The entire code to build this

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String
  
  'Need final filter
  Dim strFilter As String
  Dim AndOr As CombineFilterType
  
  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select
  
  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
  strFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)
  
  GetFilter = strFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
  
End Function

 

[Isaac]

Nice job MajP. I like what you said and think the same way when it comes to 1) single quotes, and 2) using Form! references in queries.

 

[Mike Krailo]

This is outstanding MajP. I'll start using this right away.

 

[MajP]

Part 3: Combining Several Control Filters

In part 2 (using part 1) I demonstrated how to get a single filter string from a control. Most of the time you have multiple controls and you want to combine into an overall where statement. It gets tricky when one or more controls could be left empty, but my technique makes this very simple.

Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String
  
  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If
  
  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

To make this more flexible I provide a means to specify if the individual filters can be combined with an "AND" or an "OR"

From the demo here is the code on the form to take the individual filters and combine them

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String
  
  'Need final filter
  Dim strFilter As String
  Dim AndOr As CombineFilterType
  
  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select
  
  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
  strFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)
  
  GetFilter = strFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
  
End Function

That is the technique I use every time. It is basically one line of code per control, regardless of control type.
1. Define a variable for each filter (example strDate)
2. Call the functions from Part 2 to get a properly formatted and delimited satement for each control
NOTE: The big trick is that if the control is left empty its its filter string is just an empty string. This gets ignored when combined
3. Use the CombineFilters method to combine as many filter strings as you want.

So if in the above example
strName = "Full_Name = 'Adam Graham'"
strSate = ""
strCountry = "Country = 'Armenia'"
strDate = ""
strID = ""
Then
CombineFilters(ct_OR, strName, strState, strCountry, strDate, strID)

returns

Full_Name = 'Adam Graham' OR Country = 'Armenia'

 

[Pat Hartman]

I recommend only single quotes (works always).

Sorry Maj. Single quotes will NOT work if the string contains single quotes as it would if the name were O'Tool or if it contained measurements in feet. such as 5' 2", eyes of blue

 

[Pat Hartman]

Sorry, I really don't have time to download and create a test bed for every piece of sample code that gets posted.

I looked at your documentation which said nothing about your code escaping the single quote. The directions sound like the programmer needs to double the single/double quotes manually. You might want to also escape the double quotes in the code.

 

[Pat Hartman]

You are never have time to download code or look at what is posted, but you are always willing to throw your critiques and run your mouth.

You can be snarky if you want but this is what your directions say

Strings: Strings used in SQL must be wrapped in quotes, and I recommend only single quotes (works always). If that string contains a single quote it needs to be replaced by 2 single quotes. Ex
“…WHERE Some Field = ‘Dog’…”
If the original value is O’Brien it needs to get converted to
“…WHERE SomeField = ‘O’’Brien’…”
You also want to make sure to trim the values so you do not get mistakenly
“…WHERE SomeField = ‘ Dog ’…”

I can't test the code without copying it into a database and then trying to use it.

You could have said. "Thanks Pat, the code handles the single quotes correctly but not the double quotes. I'll modify the directions to point that out."
But you chose, snarky instead. OK. Merry Christmas to you anyway.

 

[MajP]

As mentioned single quotes must get converted to two single quotes for use in a sql string. The Code as written handles single quotes within a string by converting to two single quotes.

if that string contains a single quote it needs to be replaced by 2 single quotes. Ex

If the original value is O’Brien it needs to get converted to
“…WHERE SomeField = ‘O’’Brien’…”

So
CSql("O'Brien") will return
'O''Brien'

 

[Pat Hartman]

Maj, I am not arguing that the code doesn't fix the single quotes. My point is, you point out the problem specifically but do NOT say that you fixed it in the code. But keep arguing if you want.
Happy New Year too.

 

[Isaac]

I also always only use single quotes, and they always do work correctly, as long as anything that could be interpreted as a syntax character is properly escaped, as it always should be anyway.

 

[Pat Hartman]

@Issac. Either single or double quotes will "always" work as long as you don't encounter a string with that value And You CANNOT escape the quote values without code. OBVIOUSLY you would escape them if you were using a literal but we're talking about variable data not literals.

 

[oleronesoftwares]

Generally a good piece @MajP

 

[Isaac]

@Issac. Either single or double quotes will "always" work as long as you don't encounter a string with that value And You CANNOT escape the quote values without code. OBVIOUSLY you would escape them if you were using a literal but we're talking about variable data not literals.

Right, and you can't escape the doubles without code, either.

So the best principle IMO is, just always escape.

Not sure what you mean by contrasting 'variable' with 'literal'. In my projects, variables become literals. They go hand in hand.

 

[isladogs]

 

[Pat Hartman]

The two words have very specific meanings.
Literal = some string in quotes
Variable = some field/column/control/ whatever that you reference by name to obtain its contents.

You and Maj keep saying you ONLY use single quotes and they ALWAYS work like they are magic or something. My point is that which ever delimiter you choose will work for most strings but break for others. In fact the double quotes are more likely to work than single quotes because single quotes are used in last names and they are also used in text when we write sentences. Two things that exist in most applications we work with. Double quotes are NOT used in people's names and rarely used in text unless the text is trying to quote a book title or something a person said The only time I've actually had a problem with double quotes is in engineering applications when working with variables that might refer to measurements such as inches.

So, in fact, the recommendation should be to use double quotes rather than single because they are LESS likely to run into issues unless you are working with specific types of measurement data.

The place you might have trouble with double quotes is when you are trying to quote a string inside a string as you frequently do when writing embedded SQL (which I almost never do as I have explained many times).

I solve this problem simply by creating a constant in a standard module in all my databases.

Public Const QUOTE = """"

That way, I can create a string within a string this way:

strWHERE = " WHERE LastName = " & QUOTE & Me.LastName & QUOTE

It is much clearer than using the chr() values to embed the necessary single/double quotes.

If you use single quotes in this situation, I promise that you will eventually run into an O'Tool and the code will break UNLESS you know enough to escape the single quote in Me.LastName and I seriously doubt that many Access developers reading this would actually think of that or even know what we are talking about. It might take years depending on where you live and the concentration of the Irish in your local population since Irish names are the main culprits.

I see that Colin just posted a video explaining how to escape single quotes. That works but you will need to use it religiously and not just for lastName and CompanyName. You need to use it EVERY TIME you have a string that MIGHT contain embedded single quotes.

 

[Isaac]

As usual with programming, a scenario which has the potential to happen even only one time must be accommodated. Thus, both a single quote or a double quote must be escaped as any other syntactical that could bomb the program. I do understand what you are saying about whether single or double quotes will be more common in typical entered users text, but I am not sure that it matters as long as either one could occur even one time.

As I assume Collins video demonstrates, the simple answer for any of these in Access SQL is simply doubling up.

The reason it is easier in my opinion to always use single quotes rather than double quotes in Access SQL is because it is then very easy to differentiate syntax quotation marks in your VBA project versus SQL text delimiters.

If you always use double quotes but then you have to quote the double quote in code, you end up with an ugly looking mess of multitudinous double quotes. I think maybe that's why we are saying what we are saying, but that might just be me.

I find it much easier to look at a quoted line of VBA code with a single quote inside it, then a quoted line of VBA code with additional double quotes which are intended to be literal, inside it.

That may also be unique to me, because as I have mentioned a lot on this site, after years of experience I have discovered that readability is paramount and I prioritize it before quite a few other things. The experience that I reference is specifically the experience of changing jobs very many times and having to both hand off my work to my successor as well as take over my work from a predecessor. Thus I have made certain conclusions about aspects of programming that are particularly relevant to passing work back and forth between people which is a big need and concern of mine as I have discovered it's very important to most employers.

I guess in that aspect I have been lucky because I've done a lot of contracting which has enabled me to hold many different roles even in a relatively short time span of years. While some view it as unstable, I view it as exponentially multiplying the exposure that I have to different companies and how many ways people do certain things. I've also enjoyed a couple bigger blocks of permanent full-time which has its benefits too. But the biggest benefit to my skill set and perspective has generally been the changing of roles.

Anyway, enough about that, but just explaining why readability is so important to me.
From what I have found from forums and sharing with colleagues is that the most common reason that using double quotes is more common is simply because many people don't actually know you can use single quotes, and only find out after a certain amount of experience. Which is also, as I found, the usual reason for people who eschew code, they're just not comfortable with it yet and so they see it as an odd and unfamiliar tool which is too complicated and inconvenient. Which of course, every unfamiliar tool is.

 

[MarkK]

IMO, delimiter issues in SQL are more far more easily and robustly solved by using a parameterized DAO.QueryDef.

 

[MajP]

IMO, delimiter issues in SQL are more far more easily and robustly solved by using a parameterized DAO.QueryDef.

OK, I guess. But kind of limited in the scope of this thread. I can think of way more times in Access I need to delimit something and a parameterized query is not really in play. You are unlikely/not going to use parameters in a dynamic form filter, control rowsource, form recordsource, criteria of a domain function, docmd etc. I assume the link that brought you here had to do with the critieria of a domain function which is not a candidate for a parameter query. Not saying you cannot force a parameter query into these situations, but unlikely that is going to be "easier".

For more complex action queries with lots of parameters to eval then sure it makes sense. Use something like this as a helper.

Query to find records not between two dates

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it. I don't know what the actual field names are, but the logic is: < Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)

www.access-programmers.co.uk

 

[MarkK]

delimiter issues in SQL

The scope of my comment.