Results 1 to 4 of 4

Thread: IIf Statement

  1. #1
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question IIf Statement



    I am trying to create an expression in a query, if "Roles" in table "Schedule" shows "PM" and "Role" in table "Contacts Extended" is not equal to "PM" then give me the "Rate of Pay" + $2 otherwise give me "Rate of Pay." I keep getting an #Error message in the "Rate" column

    Rate: IIf(([Schedule.ROLEs]="PM") And ([Contacts Extended.Role]<>"PM"), ([Rate of Pay]+2),[Rate of Pay])


    PM is coming from table Roles as a lookup in both tables, and shows as Text.

    HELP!!!


  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by brandy9271 View Post


    I am trying to create an expression in a query, if "Roles" in table "Schedule" shows "PM" and "Role" in table "Contacts Extended" is not equal to "PM" then give me the "Rate of Pay" + $2 otherwise give me "Rate of Pay." I keep getting an #Error message in the "Rate" column

    Rate: IIf(([Schedule.ROLEs]="PM") And ([Contacts Extended.Role]<>"PM"), ([Rate of Pay]+2),[Rate of Pay])


    PM is coming from table Roles as a lookup in both tables, and shows as Text.

    HELP!!!

    First, change your syntax to this: [Schedule].Roles and [Contacts Extended].Role

    The other thing is that you appear to be using a Lookup, so you may be disguising the datatype of Role (or Roles) in the tables. For example, what datatype is Schedule.Role if you look in the table in design mode?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    brandy9271 (2011-10-09)

  4. #3
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Schedule.Role is showing as text in the table design mode and a combo box look up, now I am getting the following error

    New Picture.png

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The error message in you post tells us that role in contacts extended is a multi value field.
    How do you expect this to work with a multi valued field?
    Which value should it be compared with?
    Regards
    John



Posting Permissions

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