Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    orinoko0 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5

    Question Form search code OR and AND combination.


    Dear All,

    I have search form with many fields on it. The majority of fields should work with AND operator, But I also have several fields on form where I enter several criteria for the same field and that several criterias should work with OR operator.
    I am new to VBA for Access and only can understand and read the code, so I find the code for AND operator, tried to combine the code with OR, but my attempt failed. So I kindly ask you to help me with it.
    Please see example of code below. and please advise how can I insert there a code for Qual1, Qual2 and Quial3 with OR.

    Code:
    Private Sub Search_Click()
    On Error GoTo Err_Search_Click
    
    
    Dim strWhere As String
    Dim lngLen As Long
    Const ConJetDate = "\#mm\/dd\/yyyy\#"
    
    
    
    
       
    '--------------------------------DATES SECTION------------------------------------------
    
    
    
    
    'BirthDate Field
    
    
    If Not IsNull(Me.BirthDate1) Then
            strWhere = strWhere & "([BirthDate] >= " & Format(Me.BirthDate1, ConJetDate) & ") AND "
       End If
    
    
    
    
    If Not IsNull(Me.BirthDate2) Then
            strWhere = strWhere & "([BirthDate] <= " & Format(Me.BirthDate2, ConJetDate) & ") AND "
       End If
       
    
    
    
    
    'TCAcceptDate Field
    
    
    If Not IsNull(Me.TCAcceptDate1) Then
            strWhere = strWhere & "([TCAcceptDate] >= " & Format(Me.TCAcceptDate1, ConJetDate) & ") AND "
       End If
    
    
    
    
    If Not IsNull(Me.TCAcceptDate2) Then
            strWhere = strWhere & "([TCAcceptDate] <= " & Format(Me.TCAcceptDate2, ConJetDate) & ") AND "
       End If
       
    
    
    'ContSentDate Field
    
    
    If Not IsNull(Me.ContSentDate1) Then
            strWhere = strWhere & "([ContSentDate] >= " & Format(Me.ContSentDate1, ConJetDate) & ") AND "
       End If
    
    
    
    
    If Not IsNull(Me.ContSentDate2) Then
            strWhere = strWhere & "([ContSentDate] <= " & Format(Me.ContSentDate2, ConJetDate) & ") AND "
       End If
       
    
    'MANY OTHER FIELDS WITH THE SAME AND LOGIC
    
    
    lngLen = Len(strWhere) - 5
    
    
    
    
    If lngLen <= 1 Then
    
    
    MsgBox "Enter Criteria!.", vbInformation, "No criteria indicated!"
    Else
    strWhere = Left$(strWhere, lngLen)
    
    
    End If
    
    
    If IsNull(Me.Qual1) = False Or IsNull(Me.Qual2) = False Then strWhere = strWhere & ")"
    
    
    DoCmd.OpenForm "frmAllData", acFormDS, , strWhere
    
    
    Exit_Search_Click:
    Exit Sub
    Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click
    
    
    End Sub
    Thank you in advance!!!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Uncle Gizmo (Tony Hine) has a number of points and youtube videos related to Searching with MS Access.
    This is not directly answering your question, but points to lots of detailed info that will help with searching and coding.

    Good luck.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    query criteria use the same order of processing as in maths and as in maths you use brackets to manage that order. For each criteria a true or false is returned i.e. -1 or 0.

    AND equates to plus in maths and OR equates to multiply

    so

    -1 AND 0 OR -1

    would equate to

    (-1+0) * -1=+1

    or it could equate to

    -1+ (0 * -1)=-1 - a different result

    If no brackets are used, the 2nd option is applied as in maths (multiplication before addition)

    Multiple AND's are straightforward, no brackets required. Same for multiple OR's. But when you need to combine AND's and OR's you need to use brackets to define the order of processing.

    another way to picture it, perhaps more aligned to your coding

    A
    AND B
    AND (C OR D)
    AND E
    AND F
    AND (G OR H)

  4. #4
    orinoko0 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    orange
    Uncle Gizmo (Tony Hine) has a number of points and youtube videos related to Searching with MS Access.
    This is not directly answering your question, but points to lots of detailed info that will help with searching and coding.

    Good luck.

    Thank you!


    Quote Originally Posted by Ajax View Post
    query criteria use the same order of processing as in maths and as in maths you use brackets to manage that order. For each criteria a true or false is returned i.e. -1 or 0.

    AND equates to plus in maths and OR equates to multiply

    so

    -1 AND 0 OR -1

    would equate to

    (-1+0) * -1=+1

    or it could equate to

    -1+ (0 * -1)=-1 - a different result

    If no brackets are used, the 2nd option is applied as in maths (multiplication before addition)

    Multiple AND's are straightforward, no brackets required. Same for multiple OR's. But when you need to combine AND's and OR's you need to use brackets to define the order of processing.

    another way to picture it, perhaps more aligned to your coding

    A
    AND B
    AND (C OR D)
    AND E
    AND F
    AND (G OR H)

    Thank you for the reply, but I have no idea how to solve the following problem: as you can see from my code it cuts several symbols from the end of string, but if I include OR statement then I will have 2 parentheses instead of 1, it means that I need to have IF expression to consider extra symbol at the end of string, but I don't know how to write code, and even more I am not sure that this solution is acceptable. It would be perfect if you could advise me possible solution.

    Thanks again for you time

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 4 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    orinoko0 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    Dear friends,

    Still struggling with this issue.

    For this line

    Code:
    A 
    AND B
    AND C 
    AND (G OR H OR I OR J) 
    I check if the field is blank and if it is I subtract 5 symbols from the end.
    But I do not know how to remove first paretheses of OR expression if all Or fields are blank.
    Please give me some direction.

    Thank you!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    if the field is blank and if it is I subtract 5 symbols from the end.
    Please explain. What field?
    How about providing a few examples of what you are trying to achieve.

  8. #8
    orinoko0 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    Quote Originally Posted by orange View Post
    Please explain. What field?
    How about providing a few examples of what you are trying to achieve.
    Here is a part of code. I don't know how to remove parentheses when no OR criteria is used, eg. all fields are blank. Parentheses are indicated in red in code.
    Please help.

    Code:
    '-----------
    
    'RPLandlordDocs field
    If Not IsNull(Me.RPLandlordDocs) Then
    strWhere = strWhere & "([RPLandlordDocs] like ""*" & Me.RPLandlordDocs & "*"") And "
    End If
    
    
    
    
    'RPStatus field
    If Not IsNull(Me.RPStatus) Then
    strWhere = strWhere & "([RPStatus] like ""*" & Me.RPStatus & "*"") And "
    End If
    
    
    
    
    
    
    '--------------------OR SECTION-------------------------
    
    
    
    strWhere = strWhere & "("
    
    
    'Qual1 field
    Label2: If Not IsNull(Me.Qual1) Then
    strWhere = strWhere & "([Qual] like ""*" & Me.Qual1 & "*"")  OR "
    End If
    
    
    
    
    'Qual2 field
    If Not IsNull(Me.Qual2) Then
    strWhere = strWhere & "([Qual] like ""*" & Me.Qual2 & "*"")  OR "
    End If
    
    
    lngLen = Len(strWhere) - 5
    
    
    
    
    If lngLen <= 1 Then
    
    
    MsgBox "Enter Criteria!.", vbInformation, "No criteria indicated!"
    Else
    strWhere = Left$(strWhere, lngLen)
    
    
    End If
    
    
    If IsNull(Me.Qual1) = False Or IsNull(Me.Qual2) = False Then strWhere = strWhere & ")"
    
    
    DoCmd.OpenForm "frmAllData", acFormDS, , strWhere
    
    
    Exit_Search_Click:
    Exit Sub
    Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click
    
    
    End Sub

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can either use a second variable for the OR section, and only add the parentheses if it gets populated, or test all the OR controls and make sure one is populated before adding the parentheses.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My curiosity is piqued. I have always considered AND or OR to be strictly logical operators in Access, not behaving as mathematical operators. They are clearly defined as such here

    So I tried the following in the immediate pane
    ?-1 AND 0 OR -1
    -1
    ?-1 AND (0 OR -1)
    -1
    ?(-1 AND 0) OR -1
    -1
    The last one doesn't seem to follow the convention being described where
    (-1+0)*-1
    = -1*-1 = 1 Clearly 1 in the post and -1 in the immediate pane are not the same mathematical result.

    However, if I consider them to be logical operators (and it helps to think of -1 and 0 as True/False being performed on 2 inputs or operands), then do the above results in the immediate pane make sense? The window output is in red bold...

    ?true and false or true
    True : (-1) Processed in non grouped order, True AND False [= false], the remainder is processed as False OR True [= True]; thus the answer of True OR False makes sense as being True. To elaborate one time, 2 inputs compared cannot be true AND false at the same time, thus the comparison is False. For the remainder, 2 inputs compared can be True OR False, thus the answer is True (-1)

    ?true AND (false OR true)
    True : (-1) processed as grouped, as True AND True since (False OR True) is True

    Now for the last one that didn't work:
    ?(true and false) or true
    True : (-1) processed as grouped, (True and False) [=False], the remainder is False or True, which is True

    Thus it makes sense to me if I think of them as logical operators, but not as mathematical operators, even though -1 in the immediate pane AND +1 in the post are both TRUE. They just aren't the same mathematical result. Anyway, that's just my humble opinion for what it's worth.
    Last edited by Micron; 09-20-2018 at 04:22 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Clearly 1 in the post and -1 in the immediate pane are not the same mathematical result.
    I agree, but some things to consider. In many systems, true is represented by 1 and not -1, in which case the result would have been 1. Also the evaluation of a Boolean is effectively 0=false, anything else (other than null) is true

    ?cbool(1)
    True
    ?cbool(0)
    False
    ?cbool(222)
    True

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agreed on the values of true/false, which is why I didn't post what happened when I used 2 in place of one of the values.
    So - you're agreeing that it's not math at all, but it's boolean logic?

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I agree as well. I've only used them as logical operators.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    orinoko0 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    You can either use a second variable for the OR section, and only add the parentheses if it gets populated, or test all the OR controls and make sure one is populated before adding the parentheses.
    Thank you very much, solved the problem using second way. I am so so so happy Thanks a lot!!!!!!

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    So - you're agreeing that it's not math at all, but it's boolean logic?
    yes - I was just using maths to illustrate the use of boolean logic - which equates to maths in terms of ordinality of expression and use of brackets to control the order of evaluation. Clearly in boolean logic (-1 or 0) will return true, (-1*0) will return 0 (false)

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

Similar Threads

  1. How to code different search boxes on same form
    By vector39 in forum Programming
    Replies: 2
    Last Post: 09-22-2017, 12:48 PM
  2. VBA code for search button in a form
    By alicevuap in forum Access
    Replies: 5
    Last Post: 03-04-2016, 09:29 AM
  3. Replies: 10
    Last Post: 07-01-2014, 11:05 AM
  4. Code for search form error
    By jfn15 in forum Forms
    Replies: 4
    Last Post: 06-11-2013, 09:02 AM
  5. Replies: 1
    Last Post: 01-09-2013, 01:33 PM

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
  •  
Other Forums: Microsoft Office Forums