Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009

    Unanswered: Can I filter on the same field in a query?


    I’m wondering if it is possible to filter on the same field in a query. I have a field called “WO_Type” and there are two items that I would like to filter by in a report. I would like one column (in my report) to list “New Location” and another column to list “Existing Project” I would like both of these items listed in separate columns using the same field “WO_Type” I thought I could add the field in the query twice and rename them then perform my filter. Ex:

    New Location: [WO_Type]
    Criteria: “New Location

    Existing Project: [WO_Type]
    Criteria: “Existing Project”

    I tried the above, but it did not work.
    Any suggestions will be greatly appreciated.

    Thank you

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Well you can, but what would be the point? The two fields would contain the exact same information, so filtering one would be enough.... and if you specified two different criteria, you'd get no data back.

    My guess is that I don't get what you are trying to do.

    If all you want to do is to get back all records where WO_Type is either "New Location" or "Existing Project" then all you need to do is set your criteria for the one WO_Type column to "New Location" or "Existing Project".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2009
    Well, I actually need both items in seperate columns. I need to count how many New Location for the monthe and how many Existing Projects for the month. Is there a better way to do this?

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    Try this:
    SELECT Table1.WO_Type, IIf([WO_Type]="New Location",[WO_Type],"") AS [New Location], IIf([WO_Type]="Existing Project",[WO_Type],"") AS [Existing Project]
    FROM Table1;
    And add two text boxes based on [New Location] and [Existing Project] in your report.

    Have a nice day!

  5. #5
    Join Date
    Apr 2009


    Oh great! That is exactly what I needed.

    Thanks a bunch.

  6. #6
    Join Date
    Apr 2009
    Hi Sinndho,
    How can I count the number of "New Location" and "Existing Project" items in the query with the IIf statement?

Posting Permissions

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