Filtering Access report with multiple strings in v

I've been teaching myself Access for the past few weeks and have done well using old questions to solve a lot of the programming conundrums I encountered. I have finally hit a wall and cannot make this filter work correctly.

I'm attempting to use multiple comboboxes on a form to filter a report by a number of criteria. It works great when filtering by customer, branch, and date range, but my numerous attempts at getting G/P% range filter added in have been very frustrating.

I'm using vba to concatenate strings coming from the comboboxes to turn them into a filter, but I either get a data type mismatch error or syntax error with the G/P range function. The underlying queries have [G/P%] formatted as a percent, the comboboxes' source are value lists ("0%","5%","10%",etc.), and I was certain to leave out the single quotes to designate the values as numbers and not strings. I've been at it for many hours trying variations on this code and nothing will work. The individual strings seem okay, but combining them into a single filter to use to open the report is where I think the issue is.

I've tried using Format() to make the values percentages; I've tried setting the variables as strings, integers, and long; I've tried moving all the text down to the final string so the G/P variables are only numbers; at this point I'm convinced that the error I've made is something small and easy that I've overlooked, but I am out of ideas.

The truly frustrating thing is I have a similar code on a different part of the form that only filters with customer name and max G/P and it works perfectly!

Public Sub cmdSalesOVFilter_Click()

    Dim strBRFilter As String
    Dim strCRFilter As String
    Dim strFDateField As String
    Dim strTDateField As String
    Dim strMinGPFilter As String
    Dim strMaxGPFilter As String
    Dim strSOFilter As String

    If IsNull(Me.cboBranchSales.Value) Then
    strBRFilter = " AND [BranchName] Like '*'"
    Else
    strBRFilter = " AND [BranchName] ='" & Me.cboBranchSales.Value & "'"
    End If
    If IsNull(Me.cboCustSalesOV.Value) Then
    strCRFilter = " AND [CustName] Like '*'"
    Else
    strCRFilter = " AND [CustName] ='" & Me.cboCustSalesOV.Value & "'"
    End If
    If IsNull(Me.cboSalesOVDateFrom.Value) Then
        strFDateField = " AND [OrderDate]>#" & Format("6/1/2016", "Short Date") & "#"
    Else
        strFDateField = " AND [OrderDate]>=#" & CDate(Me.cboSalesOVDateFrom.Value) & "#"
    End If
    If IsNull(Me.cboSalesOVDateTo.Value) Then
        strTDateField = " AND [OrderDate]<#" & Format("1/1/2505", "Short Date") & "#"
    Else
        strTDateField = " AND [OrderDate]<=#" & CDate(Me.cboSalesOVDateTo.Value) & "#"
    End If
    If IsNull(Me.cboSalesOVMinGP.Value) Then
        strMinGPFilter = " AND [G/P%]>=-100"
    Else
        strMinGPFilter = " AND [G/P%]>=" & Me.cboSalesOVMinGP.Value
    End If
    If IsNull(Me.cboSalesOVMaxGP.Value) Then
        strMaxGPFilter = " AND [G/P%]<=100"
    Else
        strMaxGPFilter = " AND [G/P%]<=" & Me.cboSalesOVMaxGP.Value
    End If
    strSOFilter = Mid(strCRFilter & strBRFilter & strFDateField & strTDateField & strMinGPFilter & strMaxGPFilter, 6)

    Call ReportFilter(strSOFilter)

    DoCmd.OpenReport "rptYTDCustInvoices", acViewReport, strSOFilter
    With Reports![rptYTDCustInvoices]
        .Filter = strSOFilter
        .FilterOn = True
    End With
End Sub

The format used in the query is of no importance, so 5% will have the value 0.05.

However, a combobox always returns strings, so 5% selected returns "5%".

This you can convert to a number:

TrueValue = Val("5%") / 100

Now, this must be properly concatenated with your SQL, and the only safe method is to use Str:

TrueTextValue = Str(Val("5%") / 100)

Forget this for a moment, because your other conversion are more or less loose:

This should read:

" And [OrderDate] >= #" & Format(CDate(Me.cboSalesOVDateFrom.Value), "yyyy\/mm\/dd") & "#"

A fixed date should either be literal:

" And [OrderDate] > #6/1/2016#" or " And [OrderDate] > #2016/6/1#"

or use DateSerial:

" And [OrderDate] >= #" & Format(DateSerial( 2016, 6, 1), "yyyy\/mm\/dd") & "#"

For the general formatting of value for concatenating, you can take advantage of my CSql function. The in-line comments list typical usage:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function