locked
Query criteria RRS feed

  • Question

  • I don't understand simply thing.

    I need something like this. 

    Field1(criteriaA OR criteriaA) AND Field2(criteriaC OR criteriaD)...

    it works for one field Field1(criteriaA OR criteriaA) and when I add criteriaC on Field2 it looks like 

    (Field1.criteriaA AND Field2.CriteriaC) OR Field1.CriteriaB

    Monday, November 9, 2020 11:18 AM

All replies

  • Field1(criteriaA OR criteriaA) AND Field2(criteriaC OR criteriaD)...

    Hi vlada,

    The AND operator is used before the OR operator, but after round brackets.

    You need something like:

        (Field1.CriteriaA OR Field1.CriteriaB) AND (Field2.CriteriaC OR Field2.CriteriaD)

    Imb.

    Monday, November 9, 2020 12:02 PM
  • Dear Imb.

    Yes I understand that but can't find solution. I can't post picture so I try to explain.

    I wrote criteria on one Field like this: 

    [Forms]![Search Form]![ComboMachineName] Or [Forms]![Search Form]![ComboMachineName] is Null

    so it works until one moment when Access alone create new field [Forms]![Search Form]![ComboMachineName] and inside OR criteria wrote IsNull. And delete everything  after OR in first field. It still works for that one field but I cant combine to got( _or_) and _.

    Monday, November 9, 2020 12:37 PM
  • [Forms]![Search Form]![ComboMachineName] Or [Forms]![Search Form]![ComboMachineName] is Null

    Hi vlada,

    You can try:

    ... ([Forms]![Search Form]![ComboMachineName] IS NULL OR [Forms]![Search Form]![OtherName] IS NULL) AND ( ... OR ... ) 

    or

    ... ([Forms]![Search Form]![ComboMachineName] IS NULL OR [Forms]![Search Form]![ComboMachineName] = 'MachineName') AND ( ... OR ... ) 

    You can not OR the controls, each control with its own criterium can be OR'ed.

    Imb.

    Monday, November 9, 2020 1:03 PM
  • I wrote criteria on one Field like this: 

    [Forms]![Search Form]![ComboMachineName] Or [Forms]![Search Form]![ComboMachineName] is Null

    The following is my standard reply with regard to criteria like this.  I would draw your attention to the paragraph in italics, which points out the need to only enter and save such criteria in SQL view, never in query design view:

    The basis for restricting a query on multiple parameters where the parameters might be used singly or in combination, allowing one or more to be Null, is that in the WHERE clause each parameter is tested in this way:

    WHERE (SomeColumn = [some parameter]
      OR [some parameter] IS NULL)
    AND (SomeOtherColumn = [some other parameter]
      OR [some other parameter] IS NULL)
    AND etc

    The following can of course be used:

        (SomeColumn LIKE "*" & [some parameter] & "*"
          OR [some parameter] IS NULL)

    where it is appropriate to use pattern matching rather than testing for equality.  Bear in mind, however, that even when applied to a single column this can result in specious mismatches.  Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly.  In most cases it is better to reference a combo box in an unbound dialogue form, or in a bound form's header, as the parameter.  The user can then select a value from a fixed set of known values, or by entering the first few characters in the combo box, progressively go to the first match by virtue of the control's AutoExpand property.  The following query is an example:

    SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
    Country, Employer, LastName, FirstName, Contacts.ContactID
    FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)
    INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts
    ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers
    ON Contacts.ContactID = ContactEmployers.ContactID)
    ON Employers.EmployerID = ContactEmployers.EmployerID)
    ON Regions.RegionID = Cities.RegionID
    WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity
        OR Forms!frmReportDialogue!cboCity IS NULL)
    AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer
        OR Forms!frmReportDialogue!cboEmployer IS NULL);

    This example is taken from the section on 'Retrieving data from the database' in my DatabaseBasics demo file, which can be found in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient.  It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.  

    Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter or, if the parameter is left empty (NULL),  for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

    Note that when you do this, parameters should only be declared in a PARAMETERS clause if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

    When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

    Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

     WHERE (DateColumn >= [start date parameter]
      OR [start date parameter] IS NULL)
    AND (DateColumn < [end date parameter]+1
      OR [end date parameter] IS NULL)


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, November 9, 2020 11:15 PM Hyperlink inserted.
    Monday, November 9, 2020 11:13 PM