Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2016

    Unanswered: Using Nested IIF Statement in Query to bring back only records that match

    Please excuse me if this is a very basic question.

    I am writing a query that will become an append query. The field on the form (used in the expression: [Forms]![frmSynMonthYear]![SyndicationMonth]) is the milestone end date that all the tasks (the query that will become an append query). Most tasks are repeated each month but some tasks are quarterly, semi-annual or annual. I wanted to use the values from the tblTaskFeed field "FrequencyID" to filter what is appended for each month. I.e. We will need monthly, quarterly, semi-annual and annual tasks in January; monthly in February, quarterly and monthly in April and so-on.

    Here is the nested IIF statement I wrote. It brings back 0 records so obviously I did not write this correctly:

    IIF((Month([Forms]![frmSynMonthYear]![SyndicationMonth])=1),(LIKE ([tblTaskFeed]![FrequencyID]=1) AND ([tblTaskFeed]![FrequencyID]=2) AND ([tblTaskFeed]![FrequencyID]=3) AND ([tblTaskFeed]![FrequencyID]=4)),IIF((Month([Forms]![frmSynMonthYear]![SyndicationMonth])=4),(LIKE ([tblTaskFeed]![FrequencyID]=1) AND ([tblTaskFeed]![FrequencyID]=2) AND ([tblTaskFeed]![FrequencyID]=5)),IIF((Month([Forms]![frmSynMonthYear]![SyndicationMonth])=7), (LIKE ([tblTaskFeed]![FrequencyID]=1) AND ([tblTaskFeed]![FrequencyID]=2) AND ([tblTaskFeed]![FrequencyID]=3)),IIF((Month([Forms]![frmSynMonthYear]![SyndicationMonth])=10), (LIKE ([tblTaskFeed]![FrequencyID]=1) AND ([tblTaskFeed]![FrequencyID]=2)),
    (LIKE [tblTaskFeed]![FrequencyID]=1)))))

    I tried to write it so that if it was a month not listed it would just bring back the monthly tasks only ([tblTaskFeed]![FrequencyID]=1). I added the "LIKE" after reading some suggestions online but it didn't seem to make a difference whether I included it or not.

    I also saw suggestions to write a VBA function but I am not sure how to write one correctly or how to call it in the Build Expression box.

    I also tried using the SWITCH function which also brought back 0 records.

    FYI The [tblTaskFeed]![FrequencyID] field is joined with another table that lists as follows:
    ID Frequency
    1 Monthly
    2 Quarterly
    3 Semi-annual
    4 Annual
    5 Annual (April Only)

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 35
    If you need this many nested IFs, then you need
    A lookup table to convert the values to text, in a query.

  3. #3
    Join Date
    Oct 2016

    Thank you

    Thank you Ranman! I have never done a lookup table but will try this. I was wondering if when I was putting in the [fieldname]=1 (or other number) if I was telling Access to make that the field value instead of bringing back that field where it equals that number. I will let you know if using the lookup table works (as in, if I do it correctly). Thank you again!

Tags for this Thread

Posting Permissions

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