Results 1 to 12 of 12
  1. #1
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107

    And Or Criteria

    Hello,

    I'm trying to build a query that has two "And" Criteria and ten "Or" criteria. This is for a search box that I have built. I have tried several approaches but for some reason the two "And" criteria are bypassed. I was thinking to try building a query to filter the two And criteria first and then building another query with the Or criteria. But this is not working as well. Does anyone have any experience with this?


    SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project, qrySapMhrs.SumOfPlannedHours, SumScore.pctComp FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (((tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) LEFT JOIN SumScore ON tblOrderDetails.OrderNum = SumScore.OrderNum) LEFT JOIN qrySapMhrs ON tblOrderDetails.OrderNum = qrySapMhrs.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification

    WHERE (((tblProjectStatus.DateClosed) Is Null)
    And ((tblOrderDetails.OrderNum) Is Not Null))
    Or (((tblOrderDetails.OrderNum) LIKE '*TA1*')
    And ((tblOrderDetails.WBS) LIKE '*TA1*')


    And ((tblOrderDetails.Sortfield) LIKE '*TA1*')
    And ((tblObjectStatus.Status) LIKE '*TA1*')
    And ((tblOrderNotifications.Notification) LIKE '*TA1*')
    And ((tblOrderDetails.Revision) LIKE '*TA1*')
    And ((tblOrderDetails.OrderType) LIKE '*TA1*')
    And ((tblOrderNotifications.CreatedBy) LIKE '*TA1*')
    And ((tblOrderDetails.PlannerGroup) LIKE '*TA1*')
    And ((tblOrderStatus.AssignUsername) LIKE '*TA1*'))
    Order BY tblOrderDetails.Revision ASC

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Remove all the extraneous brackets, Access LOVES adding brackets to Where clauses , and then you might see more clearly.

    At the moment you have (2 ANDS) Or (10 ANDS) I doubt that is your intention.
    This will be incredibly slow on a large dataset.

    Consider building the Where clause dynamically based on the actual requirement, this will a) be cleaner, b) more efficient.
    Have a look here http://allenbrowne.com/ser-62.html
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    With Allens example. Lets say I have 5 text boxes for search criteria to be entered.


    The first 4 boxes are specific searches
    - Revision
    - Plant
    - Planner
    - Order

    The fifth text box is a "contains text box" where the user can add anything into the filter and it will look in multiple fields and filter only the ones that contain the search criteria. How would I do this with the code? I tried to duplicate it but it did not work. Also to note that this filter may contain text and number fields.


    If Not IsNull(Me.TxtRevision) Then
    strWhere = strWhere & "([Revision] = """ & Me.TxtRevision & """) AND "
    End If


    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtPlant) Then
    strWhere = strWhere & "([WBS] Like ""*" & Me.TxtPlant & "*"") AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtPlanner) Then
    strWhere = strWhere & "([PlannerGroup] Like ""*" & Me.TxtPlanner & "*"") AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtContains) Then
    strWhere = strWhere & "([OrderNum] Like ""*" & Me.TxtContains & "*"") AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.TxtContains) Then
    strWhere = strWhere & "([CreatedBy] Like ""*" & Me.TxtContains & "*"") AND "
    End If

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can't search for text in a number field. Well you can but you won't get any results, so don't try. "1" will never find 1 in a number field. In fact you'll probably get an error.

    Do you really need to search every field in that list for your search text? On top of your other filters ? Are your users savvy enough to be slightly more intuitive about what they are looking for ?
    For instance, your CreatedBy field - Surely you can simply create a combo for that listing everyone that is in your organisation that has created an order, and use that as a = filter?

    Order numbers tend to be pretty unique per specific customer, so why not filter by customer then cascade filter a combo that only lists that customers order numbers ?

    Rather than making a universal super slow search, validate and limit your searches to real world examples.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Ok I understand the limitations of access. Is there a way to allow the user to export the filtered result in excel?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Ok I understand the limitations of access. Is there a way to allow the user to export the filtered result in excel?
    it is not a limitation of access, though I guess you could argue it is a limitation of any database - but you try to do it in say excel...... There are a number of ways the result can be exported to access, easiest is to use transferspreadsheet to export the query.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Minty View Post
    You can't search for text in a number field. Well you can but you won't get any results, so don't try. "1" will never find 1 in a number field.
    I believe you can if you convert the number to text in the query field: Cstr(Me.myField). However, if you're going to allow use to type in anything, then you'd first have to test for what the control contains before applying any conversion function. Not that I disagree with anything else that's been said.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You can't search for text in a number field
    actually, you can

    ?123 like "*1*"
    True

    ?123 like "*13*"
    False

    ?123 like "*1?3*"
    True

  9. #9
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by sparker75 View Post
    Ok I understand the limitations of access. Is there a way to allow the user to export the filtered result in excel?
    Yes this is how I handle simpler "reporting" requirements.
    Instead of trying to please all the people all the time with a search query that can do everything, I make generic queries that can be limited to specific periods with a date selector, and if required can be limited to a single customer account or country.

    These can be viewed on screen as an non-editable query result, or if a checkbox is ticked, automatically exported to an Excel sheet in a formatted table, that can then be filtered to the end users specific needs.
    Just to keep paranoia at bay, and to ensure the reports written are fit for purpose(e.g. actually used...), I record the date it was used, who ran it, and if it was exported to Excel in a logging table.

    This lets me maintain the list of queries sensibly. I am going to expand this out to a more robust solution that would allow different parameters to be passed to some complicated Stored Procedure type queries, but it's not a task I've had time to work on recently.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Ajax View Post
    actually, you can

    ?123 like "*1*"
    True

    ?123 like "*13*"
    False

    ?123 like "*1?3*"
    True

    But I meant the other way around (I think)....

    ? "*1*" like 123
    False

    ? "*1*" like 1
    False
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    But I meant the other way around (I think)....

    ? "*1*" like 123
    False

    ? "*1*" like 1
    False
    ummm - well they would be false , the * only works in the like comparator (the bit after like) - "*1*" like "1" would also return false

  12. #12
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think I confused myself - It is Monday.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-15-2016, 05:56 AM
  2. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  5. Replies: 5
    Last Post: 08-02-2012, 09:44 AM

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