Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Using IIF to create Field based on Another

    Good Morning,

    I am trying to create a field in a query called "Location" using the IIF function. The field is a Text field because some of the data is alpha numeric.

    I type the function out, and when I run it it gives my True value to everything in the table and not just those I want to have it. I pasted the start of my formula below.

    Location: IIf([Visits]![Procedure Code]="59200" Or "59400","In-Patient","Other")

    Also, is there some way I can do the list, as this is only 2 of 39 for this particular piece.

  2. #2
    Join Date
    Feb 2012
    Posts
    14
    Quote Originally Posted by CHI Brian View Post
    Location: IIf([Visits]![Procedure Code]="59200" Or "59400","In-Patient","Other")
    Did you actually mean this?
    IIf( ([Visits]![Procedure Code]="59200") Or ([Visits]![Procedure Code]="59400"), "In-Patient","Other")

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    I also recived notice of a reply for use of the "In" Functions which also worked.

    However it didn't take all of my list. I have 3 sections that break down into 3 different Locations. The total number of Procedure Codes I have is 172, It took most of them but cut off after so many. Is there alimit to this and if so what would be a better way to do this that would retain all of my codes?

  4. #4
    Join Date
    Feb 2012
    Posts
    14
    Put the 172 in a table and join that to your query?

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thank you!

    I didn't think of that and I should have.

Posting Permissions

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