Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38

    Searching more than one field at a time.

    I have a following code to search various fields in a form. A user can enter in more than one fields at a time. How can I modify the code to search based on more than one field at a time. Thanks
    Private Sub Search_actor_Click()


    If IsNull(Age) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Age]='" & Me!cboAge & "'"
    End If
    If IsNull(Ethnicity) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Ethnicity]='" & Me!Ethnicity & "'"
    End If
    If IsNull(FirstName) = False Then
    DoCmd.OpenForm "Actor Search", , , "[FirstName] LIKE '*" & Me!FirstName & "*'"
    End If
    If IsNull(LastName) = False Then


    DoCmd.OpenForm "Actor Search", , , "[LastName] LIKE '*" & Me!LastName & "*'"
    End If
    If IsNull(Gender) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Gender]='" & Me!Gender & "'"
    End If
    If IsNull(UnionStatus) = False Then
    DoCmd.OpenForm "Actor Search", , , "[UnionStatus]='" & Me!UnionStatus & "'"
    End If
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the filter would be something like

    "[Age]='" & Me!cboAge & "' AND [LastName] LIKE '*" & Me!LastName & "*'"

    And you do need to decide whether you want AND or OR.

    looking for someone who is 35 and has a last name of 'Smith'

    will produce different results to

    looking for someone who is 35 or has a last name of 'Smith'

    you probably want to do you code slightly differently as well and assign to a filter string before opening the form e.g.

    Code:
    
    Dim FilterStr as String
    
    If IsNull(Age) = False Then     FilterStr="[Age]='" & Me!cboAge & "' AND "
    If IsNull(Ethnicity) = False Then   FiltrStr=FilterStr &  "[Ethnicity]='" & Me!Ethnicity & "' AND "
    ...
    ...
    
    If len FilterStr>0 then
       Filterstr=left(filterstr, len(filterstr)-5)
       DoCmd.OpenForm "Actor Search", , , FilterStr
    end if
    
    
    

  3. #3
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Thanks Ajax. It's a good idea to have a string variable. It makes sense!
    However, could you please explain why you have a -5 in the following statement. I'm not a vb programmer so I'm confused only at this step.
    Filterstr=left(filterstr, len(filterstr)-5)

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    because you are adding ' AND ' to the end of each addition. So you need to remove the last one. The actual value being supplied to the left function is len(filterstr)-5

  5. #5
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Makes sense! Thanks!

  6. #6
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    For some reason this code is not working

  7. #7
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    The following is my code and it's giving error. Please let me know what I'm doing wrong here. Thanks.
    Private Sub Search_actor_Click()



    Dim FilterStr As String


    If IsNull(Age) = False Then
    FilterStr = "[Age]='" & Me!cboAge & "'AND"
    End If
    If IsNull(Ethnicity) = False Then
    FilterStr = FilterStr & "[Ethnicity]='" & Me!Ethnicity & "'AND"
    End If
    If IsNull(Gender) = False Then
    FilterStr = FilterStr & "[Gender]='" & Me!Gender & "' AND"
    End If
    If IsNull(FirstName) = False Then
    FilterStr = FilterStr & "[FirstName] LIKE '*" & Me!FirstName & "*' AND"
    End If
    If IsNull(LastName) = False Then
    FilterStr = FilterStr & "[LastName] LIKE '*" & Me!LastName & "*' AND"
    End If
    If IsNull(UnionStatus) = False Then
    FilterStr = FilterStr & "[UnionStatus]='" & Me!UnionStatus & "'"
    End If
    If Len(FilterStr) > 0 Then
    FilterStr = Left(FilterStr, Len(FilterStr) - 5)
    DoCmd.OpenForm "Actor Search", , , FilterStr
    End If
    End Sub

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it always helps to clarify what 'not working' means and if you get an error, what the error is. So pure guess, but you have not followed my suggestion and seem to be missing some characters

  9. #9
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    I'm getting runtime error 2501. Open form option was canceled.

    Private Sub Search_actor_Click()


    Dim FilterStr As String


    If IsNull(Ages) = False Then FilterStr = "[TxtAgeRange]='" & Me!cboAge & "' AND "
    If IsNull(Ethnicity) = False Then FilterStr = FilterStr & "[Ethnicity]='" & Me!Ethnicity & "'AND "
    If IsNull(Gender) = False Then FilterStr = FilterStr & "[Gender]='" & Me!Gender & "' AND "
    If IsNull(FirstName) = False Then FilterStr = FilterStr & "[FirstName] LIKE '*" & Me!FirstName & "*' AND "
    If IsNull(LastName) = False Then FilterStr = FilterStr & "[LastName] LIKE '*" & Me!LastName & "*' AND "
    If IsNull(UnionStatus) = False Then FilterStr = FilterStr & "[UnionStatus]='" & Me!UnionStatus & "'"


    If Len(FilterStr) > 0 Then
    MsgBox "The FilterStr variable is " + FilterStr
    FilterStr = Left(FilterStr, Len(FilterStr) - 5)
    DoCmd.OpenForm "Actor Search", , , FilterStr This is where the debug is pointing at.
    End If
    End Sub

  10. #10
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    MsgBox "The FilterStr variable is " + FilterStr
    The above line is just for me to see what's inside FilterStr.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Umm, this looks like a variation of the same problem in one of your other posts https://www.accessforums.net/showthread.php?t=67523 where I asked you to use code tags.
    Are you forgetful about such requests, plus don't care that you might have several different people working on the same problem only to discover it solved in the other thread?
    Maybe you don't care, but some of us keep a list of those we won't help anymore.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you are still missing characters - which should be evident from your msgbox

  13. #13
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    So I'm still getting the error 2501 and asking me to enter a parameter for TxtAgeRange. I honestly don't know what characters I'm missing. VBA is a totally foreign language for me.
    Dim FilterStr As String

    Dim lngLen As Long


    If IsNull(Ages) = False Then FilterStr = FilterStr & "([TxtAgeRange]=""" & Me!cboAge & """) AND "
    If IsNull(Ethnicity) = False Then FilterStr = FilterStr & "([Ethnicity]=""" & Me!Ethnicity & """)AND "
    If IsNull(Gender) = False Then FilterStr = FilterStr & "([Gender]=""" & Me!Gender & """) AND "
    If IsNull(FirstName) = False Then FilterStr = FilterStr & "([FirstName] LIKE ""*" & Me!FirstName & "*"") AND "
    If IsNull(LastName) = False Then FilterStr = FilterStr & "([LastName] LIKE ""*" & Me!LastName & "*"") AND "
    If IsNull(UnionStatus) = False Then FilterStr = FilterStr & "([UnionStatus]=""" & Me!UnionStatus & """) AND "


    lngLen = Len(FilterStr) - 5
    If lngLen > 0 Then
    FilterStr = Left$(FilterStr, lngLen)
    DoCmd.OpenForm "Actor Search", , , FilterStr
    End If

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    & Me!Ethnicity & """)AND "

    enter a parameter for TxtAgeRange
    TxtAgeRange should be the name of the field in your table - previously you had age

  15. #15
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    TxtAgeRange on form2 is a bound field obtaining the result from a query.
    I know how I'm using it is wrong.
    How can I compare the results on my unbound combo box in form1 with TxtAgeRange ?
    Also, my table doesn't have an age column. I have a query that calculates the age by obtaining date_of_birth column from the table.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Searching for a non-numeric character in a field
    By DubCap01 in forum Programming
    Replies: 6
    Last Post: 02-02-2017, 05:00 AM
  2. Trouble Searching multivalued field
    By Gr0m in forum Access
    Replies: 4
    Last Post: 03-20-2013, 11:35 AM
  3. Searching by field on subform on main form
    By helen21112010 in forum Forms
    Replies: 1
    Last Post: 10-16-2012, 07:55 AM
  4. Searching marks field
    By Wilson in forum Access
    Replies: 3
    Last Post: 07-27-2012, 07:37 AM
  5. Searching a field on a record [HELP]
    By eduardo in forum Queries
    Replies: 1
    Last Post: 06-26-2012, 07:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums