Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    16

    Question Unanswered: ApplyFilter with concatenated fields

    Hello,
    I have a multi-field search box on a bound form that filters the records shown. The user can search by ID, first name, last name .. but i wanted them to be able to search by like: "lastname, firstname". Trying to concatenate lastname and firstname, but get the Run-time error 32003 The 'ApplyFilter' action requires a valid control name that corresponds to a subform or subreport.

    Have tried a variety of things like:
    DoCmd.ApplyFilter , "[CustID] Like [Forms]![frmMain]![txtMultiSearch] & ""*"" Or [PatientFirstName] Like [Forms]![frmMain]![txtMultiSearch] & ""*"" Or [PatientLastName] Like [Forms]![frmMain]![txtMultiSearch] & ""*"" Or "[PatientLastName] & ", " & [PatientFirstName] Like [Forms]![frmMain]![txtMultiSearch] & ""*"""

    It doesn't like when i add the last part.. any suggestions?

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    your code has the [Forms]![frmMain]![txtMultiSearch] INSIDE the quotes. Move to outside the quotes.
    I would use the EXACT text boxs to search in , instead of guessing.

    Test all controls for a possible filter then build the where clause.
    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
        'remove 1st And
    if len(sWhere)>0 then sWhere= mid(sWhere,5)
    
      'just use the filter
    iLen = Len(sWhere) - 5
    If iLen <= 0 Then
        me.filterOn = false
    Else
        me.filter = sWhere
        me.filterOn = true
    End If

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one dodge you can apply to ranmans code that gets rid of the need to remove a leading 'and' is to define your where clause as 'where 1=1' (thiks alwasy evaluates as true and therefore will always work as a first term in a filter or where clause, then always add the elements as ' and acolumn = 'blah'

    eg:-

    Code:
    swhere = "1 = 1"
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
    'is the filter needed? if the length of swhere is > 5 (ie more conatins more than 1 = 1 then use the filter
    If len(swhere) <= 5
        me.filterOn = false
        me-filter = " ' shouldn't be needed as gthe filter isn't applied, but set to nothing in case the filter gets switched on elsewhere
    Else
        me.filter = sWhere
        me.filterOn = true
    End If
    similarly some peolel prefer to add the text delimter using a chr$ call eg:-

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]=" & chr$(34) & cboState & chr$(34)
    ..functionally it makes no difference but it can make code more legible, and can avoid problems with chopping and changing between using a ' or " as a delimiter, or worse letting Access VBA mangle quotes into multiple pairs.

    chr$(34) returns a "
    chr$(39) returns a '
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •