Results 1 to 13 of 13
  1. #1
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84

    Criteria of All

    Quick question: in my query design view. i would like for my expression to state in Entry Door Kitchen Cabinets, etc to Kitchen flooring to say if in any of these columns it says good or replace to give me that value. Currently if i place ="good" or "Replace" in the additional fields it gives me blanks. How do i write: If in these columns it states Good or Replace give me values, if do not show the row.

    Click image for larger version. 

Name:	Criteria.PNG 
Views:	9 
Size:	9.4 KB 
ID:	24673

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Doesn't look like it should, but try:

    In("good", "replace")

    If that doesn't work, can you post the SQL of the query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    SELECT NSF_Qual.[Unit Number], NSF_Qual.[Unit Type], UCase([Hand]) AS Expr1, UCase([Scope]) AS Expr2, NSF_Qual.[Entry Door], NSF_Qual.[Kitchen Cabinets], NSF_Qual.[Kitchen Countertops], NSF_Qual.[Kitchen Sink], NSF_Qual.[Kitchen Flooring]
    FROM NSF_Qual
    WHERE (((NSF_Qual.[Kitchen Flooring])="good" Or (NSF_Qual.[Kitchen Flooring])="Replace"));

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And you get records with nothing in the [Kitchen Flooring] field? I don't see how offhand. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    I do receive information.But remember that is with me only applying the criteria(="good"or "replace") only in Kitchen Flooring. I have 70 Columns going across starting with Entry Door. I would like to apply an expression or a vba code to state if this column has Good OR Replace give me Unit Number, Type Expr1 and Expr2 to all of the data that this expression applies too which is how the below is currently showing. At this currently moment if i apply the criteria (="good" or "Replace") to each column in qry design, I receive no data when i run the query.

    Click image for larger version. 

Name:	Criteria2.PNG 
Views:	8 
Size:	24.9 KB 
ID:	24674
    This is what happens when i apply the expression all the way across starting with the entry door

    Click image for larger version. 

Name:	Criteria3.PNG 
Views:	8 
Size:	10.2 KB 
ID:	24675
    Click image for larger version. 

Name:	Criteria4.PNG 
Views:	9 
Size:	11.0 KB 
ID:	24676

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The way you have it creates an "AND" comparison. To get an "OR", put each

    "good"or "replace"

    on a different line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    By doing that, if i am understanding you. I still receive no data
    Click image for larger version. 

Name:	Criteria5.jpg 
Views:	9 
Size:	53.5 KB 
ID:	24678

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, I mean the whole

    "good"or "replace"

    on the same line for each field, but on different lines for different fields. Like:

    Click image for larger version. 

Name:	QueryExample.jpg 
Views:	9 
Size:	22.3 KB 
ID:	24679
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Got it and Perfect! Thanks

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! That was more difficult to describe than it should have been.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    1 more question. I have ran out of same when copying. Either how do you add more rows under the fields or is there a vba that will do this.

  12. #12
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    Never mind found it and i agree . Felt like charlie brown. Good Grief

  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
    Glad you got it sorted. Was out at a client's site.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  2. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  3. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  4. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  5. Replies: 1
    Last Post: 07-13-2011, 11:00 AM

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