Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: Filtering a query

    In Access 2003

    I have a query consisting of Last Name, First Name, Room, and "Significant Number"

    The significant number field can have the following values: 1,2 or 3.

    When I run the query, I get about 200 records. Most of my "Significant Number" results are 3, with only several 1 or 2s.

    So what I want is a way to SHOW All 200 names, but only show the "Significant Number" field if it is a 1 or 2 and ignore the 3s.

    I don't want to filter out and show ONLY certain personnel records. I want to show ALL records but only show something in the Sig Num field if it is outside the norm - - -to make these numbers stand out more in the report I design based on the query. if this isn't possible in a single query, them some way to make 1s or 2s show up as bold. That would work too.

    I have inherited an number of database and I just don't have much experience.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT ..., IIf([Significant Number] < 3, [Significant Number], Null) AS [Significant Number]
    Note: You should refrain from using spaces in the names of the objects.
    Have a nice day!

  3. #3
    Join Date
    Oct 2012
    Posts
    3
    Sorry but I messed up. It's the 3 entry I want not to show. Your code does not work, but not because of that.

    The field is not a number field, it's a text field. The various possibilities are 1, 2, 3 ,1c, 2c, 3c. Any one of these would be significant except the "3" entry, which is the norm. So I want these to show up blank.

    I thought if I took your code and just but quotes around the 3, maybe that would work to filter out the 3 text, but no. The significant filed is related to property so I have changed the name to PropPhase

    Like I said, I'm new to this.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What about:
    Code:
    SELECT ..., IIf(Left(Nz([Significant Number], "3"), 1) < "3", [Significant Number], Null) AS [Significant Number]
    Have a nice day!

  5. #5
    Join Date
    Oct 2012
    Posts
    3
    where should I be placing this code. In the query criteria or as a filter on the form that uses the query?

    BTW, thanks for taking the time.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You wrote that you do not want to actually filter the data set:
    Quote Originally Posted by Mixnmangle View Post
    I don't want to filter out and show ONLY certain personnel records. I want to show ALL records but only show something in the Sig Num field if it is outside the norm
    Then, this should be used in a SELECT query: in the comma-separated list of filed, replace [Significant Number] with the IIF(...) AS [Significant Number] expression.
    Have a nice day!

Posting Permissions

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