Results 1 to 4 of 4
  1. #1
    oldlearner is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    31

    Conditional Iff statement?

    I have a table which I want to update a date depending on which condition I choose from Field1. Field1 uses a text lookup list (Trigger1, Trigger2 etc..) Field2 is astart date(date type). Field3 (DateType) is where i want to display my End Date result. I used the expression builder in criteria for field 3IIF([Field1]="trigger1",Field2+11,[Field1]="Trigger2",Field2+15)) but get an error message. essentially what I am trying to do is display a result in field 3 that add 11 days onto the start date if "trigger1" is used and 15 days if "triggger2" is selected. Any help gratefully received.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    Code:
    field3: IIF([Field1]="trigger1",DateAdd("d",11,[Field2]),DateAdd("d",15,[Field2]))
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    clarification

    I'm reading that Field1 is a lookup field in a table. Since I would not use them I have to ask, can you do a DLookup on a lookup field at all? If I've read the post wrong then ignore that. However, don't ignore this piece of advice please - when you get an error, post the text (and number if applicable) to eliminate a few wild guesses like mine.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    another way of accomplishing the same thing

    Code:
    field3: dateadd("d",IIF([Field1]="trigger1",11,15),[Field2]
    )

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

Similar Threads

  1. Replies: 4
    Last Post: 09-01-2019, 11:19 AM
  2. Conditional Statement
    By fucell26 in forum Access
    Replies: 3
    Last Post: 07-09-2014, 07:57 AM
  3. Help with conditional statement in query
    By batowl in forum Queries
    Replies: 1
    Last Post: 08-23-2011, 02:00 PM
  4. Conditional Select Statement
    By shexe in forum Queries
    Replies: 4
    Last Post: 09-22-2010, 09:10 AM
  5. Help with Conditional statement in query
    By Rhues in forum Queries
    Replies: 1
    Last Post: 01-11-2010, 02:09 PM

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