Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Search with multiple, optional variables

    I am trying to search a database for records based on 4 search options: Advisor Name, User Name, Account Number and/or Date. The user can enter as few or as many of these options as they would like, in any combination. I am using the below code and it works but it is very cumbersome. I am new to access and trying to learn more efficient ways of writing code. Is there a simpler way to do this? Thanks!

    Code:
    Private Sub Search_Click()
    
    
    
        With CodeContextObject
        ' All filters used
        If ComboAdvisor.Value <> "" And ComboUser.Value <> "" And TextDate <> "" And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" & .ComboAdvisor & "'And Date= #" & TextDate & "# And Account_Number=" & .TextAccount
        End If
         
         ' All filters minus date
          If ComboAdvisor.Value <> "" And ComboUser.Value <> "" And IsNull(TextDate) And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" & .ComboAdvisor & "'And Account_Number=" & .TextAccount
           End If
        
         ' All filters minus account
          If ComboAdvisor.Value <> "" And ComboUser.Value <> "" And TextDate <> "" And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" & .ComboAdvisor & "'And Date= #" & TextDate & "#"
           End If
        
         ' All minus advisor
        If IsNull(ComboAdvisor) And ComboUser.Value <> "" And TextDate <> "" And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Date= #" & TextDate & "# And Account_Number=" & .TextAccount
        End If
        
          ' All minus reviewer
        If ComboAdvisor.Value <> "" And IsNull(ComboUser) And TextDate <> "" And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "Customer_Name='" & .ComboAdvisor & "'And Date= #" & TextDate & "# And Account_Number=" & .TextAccount
        End If
        
        ' Advisor and Reviewer filter only
          If ComboAdvisor.Value <> "" And ComboUser.Value <> "" And IsNull(TextDate) And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" & .ComboAdvisor & "'"
        End If
        ' Advisor and Account
        If ComboAdvisor.Value <> "" And IsNull(ComboUser) And IsNull(TextDate) And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "Customer_Name='" & .ComboAdvisor & "' And Account_Number=" & .TextAccount
        End If
        'Advisor and Date
        If ComboAdvisor.Value <> "" And IsNull(ComboUser) And TextDate <> "" And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "Customer_Name='" & .ComboAdvisor & "' And Date= #" & TextDate & "#"
        End If
        'Reviewer and Account
          If IsNull(ComboAdvisor) And ComboUser.Value <> "" And IsNull(TextDate) And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Account_Number=" & .TextAccount
        End If
        'Reviewer and Date
        If IsNull(ComboAdvisor) And ComboUser.Value <> "" And TextDate <> "" And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Date= #" & TextDate & "#"
        End If
        'Account and Date
        If IsNull(ComboAdvisor) And IsNull(ComboUser) And TextDate <> "" And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "Date= #" & TextDate & "# And Account_Number=" & .TextAccount
           End If
        
        ' Advisor filter Only
        If ComboAdvisor.Value <> "" And IsNull(ComboUser) And IsNull(TextDate) And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "Customer_Name='" & .ComboAdvisor & "'"
        End If
        
        ' Reviewer Only
        If IsNull(ComboAdvisor) And ComboUser.Value <> "" And IsNull(TextDate) And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "'"
            End If
        ' Account Only
        If IsNull(ComboAdvisor) And IsNull(ComboUser) And IsNull(TextDate) And TextAccount <> "" Then
            DoCmd.ApplyFilter "", "Account_Number=" & .TextAccount
            End If
        'Date Only
        If IsNull(ComboAdvisor) And IsNull(ComboUser) And TextDate <> "" And IsNull(TextAccount) Then
            DoCmd.ApplyFilter "", "Date= #" & TextDate & "#"
            End If
        
        End With
    
    
    
    
    
    End Sub

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I can't really think of an easier way than the truth table style (what you're using), but, a couple suggestions:

    Use ElseIf formatting...that way when if hits the proper criteria it won't continue checking against everything else.

    Maintain a standard...you switch between IsNull and ""...personally I would switch everything that is a control (text/combo) over to IsNull, check numeric variables with 0, and string variables with "".

    So, this is what it'd roughly look like:

    Code:
    	With CodeContextObject    
    		If Not IsNull(ComboAdvisor) And Not IsNull(ComboUser) _
    		And Not IsNull(TextDate) And Not IsNull(TextAccount) Then ' All filters used
    	        	DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" _
    			& .ComboAdvisor & "'And Date= #" & TextDate & "# And Account_Number=" & .TextAccount  
          		ElseIf Not Isnull(ComboAdvisor) And Not IsNull(ComboUser) _
    		And IsNull(TextDate) And Not Isnull(TextAccount) Then ' All filters minus date
    		        DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" _
    			& .ComboAdvisor & "'And Account_Number=" & .TextAccount     
          		ElseIf Not IsNull(ComboAdvisor) And Not IsNull(ComboUser) _
    		And Not IsNull(TextDate) And IsNull(TextAccount) Then ' All filters minus account
    		        DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser & "' And Customer_Name='" _
    			& .ComboAdvisor & "'And Date= #" & TextDate & "#"     
    		ElseIf IsNull(ComboAdvisor) And Not IsNull(ComboUser) _
    		And Not IsNull(TextDate) And Not IsNull(TextAccount) Then ' All minus advisor
    			DoCmd.ApplyFilter "", "User_Last_Name='" & .ComboUser _
    			& "' And Date= #" & TextDate & "# And Account_Number=" & .TextAccount
    Just continue that logic down through the rest of them.

    Another alternative would require having at least one field to *always* check against, then it'd be an easy matter of just expanding an SQL (or in your case Filter) statement:

    Code:
    	Dim strSQL as String
    
            strSQL = "Your Beginning Filter String"
    
    	if not isnull(ComboAdvisor) then
    		strSQL = strSQL &" AND Customer_Name = '" &ComboAdvisor &"'"
    	endif
    
    	if not isnull(ComboUser) then
    		strSQL = strSQL & " AND User_Last_Name = '" &ComboUser &"'"
    	endif
    Then continue that logic through to the end. It's shorter, but, like I said, you'd have to have a beginning filter to add to.

    Sam, not much help I'm afraid, but it might give you an idea.
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    Thanks! Those are good things to know.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    That certainly is alot of code. I forget exactly what the filter should be if all the fields are blank but here is code that I have come up with based on your field names and the way i normally build a Where condition:

    Code:
        Dim strWhere As String
            
        strWhere = ""
        
        If ComboUser & "" <> "" Then strWhere = strWhere & "User_Last_Name='" & .ComboUser & "' And"
        If ComboAdvisor & "" <> "" Then strWhere = strWhere & "Customer_Name='" & .ComboAdvisor & "' And"
        If TextDate & "" <> "" Then strWhere = strWhere & "Date= #" & TextDate & "# And"
        If TextAccount & "" <> "" Then strWhere = strWhere & "Account_Number=" & TextAccount & " And"
        
        'Now trim off the last And
        if Len(strWhere)>4 Then strWhere = Left(strWhere, Len(strWhere) - 4)
        
        DoCmd.ApplyFilter "", strWhere

Posting Permissions

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