Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Query ignoring criteria

    I have finally gave in and decided to post.... All suggestion appreciated.
    I am trying to create a query that:
    1. - returns all records that have "Active" in the "Status" field
    AND
    2. - has 1 or more empty cells from from a specific range of fields.

    The below query has no problem with item 2. above, but ignors item 1. (returns records with "Inactive" in the "Status" field.)

    If I run item 1. alone, it works. (ignors every without "Active" in the "Status" field.)

    Is there another way to do this or am I missing something?

    SELECT tblPrenatalLogs.[Pat #], tblPrenatalLogs.Status
    FROM tblPrenatalLogs
    WHERE (((tblPrenatalLogs.Status)="Active") AND (([tblPrenatalLogs]![Orientation Date]) Is Null)) OR ((([tblPrenatalLogs]![ICA]) Is Null)) OR ((([tblPrenatalLogs]![First OB]) Is Null)) OR ((([tblPrenatalLogs]![Initial BH]) Is Null)) OR ((([tblPrenatalLogs]![PP BH]) Is Null)) OR ((([tblPrenatalLogs]![Nutrition]) Is Null)) OR ((([tblPrenatalLogs]![2nd CPSP]) Is Null)) OR ((([tblPrenatalLogs]![3rd CPSP]) Is Null)) OR ((([tblPrenatalLogs]![PP CPSP]) Is Null)) OR ((([tblPrenatalLogs]![Lactation]) Is Null)) OR ((([tblPrenatalLogs]![Dental Ref]) Is Null)) OR ((([tblPrenatalLogs]![CBC]) Is Null));

  2. #2
    Join Date
    Feb 2009
    Posts
    7
    I think

    AND ((

    should be

    AND (((


    With an extra ) at the end of the code

    Edit: Then it would be "This AND (this OR this OR this... OR this)"

    Although I am not sure why half of those brackets are there, but I haven't done any SQL in years.
    Last edited by steve2008; 03-02-09 at 18:30.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT tblPrenatalLogs.[Pat #], tblPrenatalLogs.Status
    FROM tblPrenatalLogs
    WHERE tblPrenatalLogs.Status="Active" AND ([tblPrenatalLogs]![Orientation Date] Is Null OR [tblPrenatalLogs]![ICA]) Is Null OR [tblPrenatalLogs]![First OB] Is Null OR [tblPrenatalLogs]![Initial BH] Is Null OR [tblPrenatalLogs]![PP BH] Is Null OR [tblPrenatalLogs]![Nutrition] Is Null OR [tblPrenatalLogs]![2nd CPSP] Is Null OR [tblPrenatalLogs]![3rd CPSP] Is Null OR [tblPrenatalLogs]![PP CPSP]) Is Null OR [tblPrenatalLogs]![Lactation]) Is Null OR [tblPrenatalLogs]![Dental Ref]) Is Null OR [tblPrenatalLogs]![CBC] Is Null);
    think of it as a boolean expression
    1. - returns all records that have "Active" in the "Status" field
    AND
    2. - has 1 or more empty cells from from a specific range of fields.

    ie
    where term1 = active AND (any of the following terms are null)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2009
    Posts
    2
    It was acutally (2) extra "(" after AND and at the end of the code. Thanks Steve2008. Thanks guys, so it is now working and looks like this:

    SELECT tblPrenatalLogs.[Pat #], tblPrenatalLogs.Status
    FROM tblPrenatalLogs
    WHERE (((tblPrenatalLogs.Status)="Active") AND (((([tblPrenatalLogs]![Orientation Date]) Is Null)) OR ((([tblPrenatalLogs]![ICA]) Is Null)) OR ((([tblPrenatalLogs]![First OB]) Is Null)) OR ((([tblPrenatalLogs]![Initial BH]) Is Null)) OR ((([tblPrenatalLogs]![PP BH]) Is Null)) OR ((([tblPrenatalLogs]![Nutrition]) Is Null)) OR ((([tblPrenatalLogs]![2nd CPSP]) Is Null)) OR ((([tblPrenatalLogs]![3rd CPSP]) Is Null)) OR ((([tblPrenatalLogs]![PP CPSP]) Is Null)) OR ((([tblPrenatalLogs]![Lactation]) Is Null)) OR ((([tblPrenatalLogs]![Dental Ref]) Is Null)) OR ((([tblPrenatalLogs]![CBC]) Is Null))));

Posting Permissions

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