Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    IIF statement to evaluate if value included in multvalued field

    I am using a multivalued field in my database and am having some issues determining if a specific value is included as a selection. I was going to use an iif statement that said iif([projects.regions] = "Asia", do something, do something else). This did not work properly. If only “Asia” was selected, this evaluated to true and it worked fine. But if there was more than one value selected in the multivalued field (“US” and “Asia”) this did not evaluate to true in the IIF statement. This is all in one table and the multiple values show up as comma dilemitted selections in the fields. I feel like the IIf statement is trying to compare the string "US" to the string "US", "EMEA", "LATAM" and evaluating false. I have tried IN and LIKE options instead of "=" in the formula and it still isn't working. My gut tells me there is an easy fix, I have spent many hours trying to figure this out. Any advice would be appreciated. Thanks in advance.

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    having multivalued fields means your database is not in first normal form, which will get you always into trouble, or did I misunderstand your explanation?

    grNG

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    grNG - Thanks for your time to reply

    I sm just a beginner but Multivalued fields are out of the box functionality with 2007. From what I have read Access maintains a table behind the scenes that resolves the many to many relationship for you. I used this option because it says it REALLY helps sharepoint front ends. It seems to work fine in the database. It has a very nice UI and If I look at my table it shows the value for region(s) as "US, Latam, Asia, EMEA" if I selected all 4. If I only select 1 or two it shows just those. That is kind of what I expected.

    I am really thinking I am doing something wrong with my comparision operation in the iif statement. I don't know VB very well and I would really like to understand if that is or isn't the problem so I can rule out that. I may need to completely go back to the drawing board with my DB design.

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

Similar Threads

  1. If Statement to validate Field on a Form
    By ETCallHome in forum Forms
    Replies: 4
    Last Post: 04-05-2011, 05:22 PM
  2. Date Field empty if statement
    By dubsdj in forum Programming
    Replies: 4
    Last Post: 03-06-2011, 04:02 PM
  3. Evaluate result of sql query
    By Tyork in forum Programming
    Replies: 2
    Last Post: 11-09-2010, 05:41 PM
  4. SQL like statement?
    By Cojack in forum Queries
    Replies: 4
    Last Post: 09-21-2010, 04:45 AM
  5. IIF statement to populate field
    By GriffyNJ in forum Queries
    Replies: 0
    Last Post: 10-13-2009, 10:07 AM

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