Work Smarter Not Harder; SQL and Filters (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:00
Joined
May 21, 2018
Messages
3,631
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.

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 ’…”

Dates: Dates give most people the biggest issue. Dates HAVE TO BE IN US FORMAT MM/DD/YYYY regardless of any REGIONAL SETTINGS or FORMATTING YOU HAVE APPLIED. I hope that is clear.
“…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. Like other conversion functions (Cdate, Clng, Ccur)
CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType)

Code:
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:
Code:
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
Code:
-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)
 

Attachments

  • MajP MultiControlSearch4.zip
    269.1 KB · Views: 0
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:00
Joined
May 21, 2018
Messages
3,631
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
Code:
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
Code:
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
Code:
 = '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
Code:
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

Lifelong Learner
Local time
Yesterday, 17:00
Joined
Mar 14, 2017
Messages
2,733
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

Member
Local time
Yesterday, 20:00
Joined
Mar 28, 2020
Messages
143
This is outstanding MajP. I'll start using this right away.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:00
Joined
May 21, 2018
Messages
3,631
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.
Code:
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
Code:
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'
 

Users who are viewing this thread

Top Bottom