Results 1 to 5 of 5

Thread: iif (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    iif (2000)

    I have a parameter query where I wish the value of the parameter to be taken from a combo box or if no selction is made in the combo from all in the underlying table thus: IIf([forms]![FrmDep]![CboFeed],[forms]![FrmDep]![CboFeed],[tblFeedTtype].[feedback ]). I need to modify this so that if the value is taken from the table then all execpt a given string in the table is returned thus in pseudo

    IIf([forms]![frmdep]![feed],[forms]![frmdep]![feed],[tbl_feed_type].[feedback type]) exect where the entry is "given string". How do I code this?

    Many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif (2000)

    I'm sorry, I don't understand this at all, neither the question, nor the pseudo code. Could you try again?

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: iif (2000)

    Yep, I'll try again, sorry Hans. I have a query where one field criteria may be taken from a combo, the fields from that combo are taken from a table. If a user doesn't select from the combo I want the query to run and display all the entries for the field from the underlying table except one of them. e.g the combo could hold milk, eggs, bacon, corn. If the user doesn't select one of these from the combo, the query runs and display all entries that include milk, eggs, bacon but not corn. I have successfully used an iif function so the query displays entries that include the selection from the combo and if the combo is not selected then all milk, eggs, bacon, corn. My problem lies with modifying the iif to exlude corn. I hope this makes more sense.

    Thanks Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: iif (2000)

    I don't think you can do this in a single expression, but it's not too difficult. Try this:

    Under the field you want to filter, enter two criteria below each other:

    [Forms]![FrmDep]![cboFeed]
    Not "excluded value"

    where "excluded value" is the string you don't want to return. In the first empty column in the query design grid, enter the parameter [Forms]![FrmDep]![cboFeed] in the Field row. Clear the Show check box, and enter Is Null in the second criteria row, leave the first one blank. So it looks like this:

    <table border=1><td align=right>Field</td><td>SomeField</td><td>[Forms]![FrmDep]![cboFeed]</td><tr><td align=right>Table</td><td>tblWhatever</td><td align=right>

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: iif (2000)

    Thanks Hans

    Darren.

Posting Permissions

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