Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    17

    Question Unanswered: Help with query - iif statement?

    Hi,

    I have a table 'Actions Log' containing, amongst others, a field called 'review date'. I also have a form 'Filter Actions Log', with a combo box (Review BA combo) where the user selects 'Before' or 'After', then a field (Review date)where he enters a date. I have a query which runs when the user clicks 'submit', now I need the query to either display all the review dates before the specified date, or after, depending on what was selected. I hope all that makes sense!

    I have been trying to use an iif statement but I think I'm using it incorrectly! I've tried:

    IIf([Forms]![Filter Actions Log]![Review BA combo]="Before", <[Forms]![Filter Actions Log]![Review Date], >[Forms]![Filter Actions Log]![Review Date])

    This clearly isn't how you use an iif statement though! I'm fairly new to all this, so any help would be very much appreciated. If I can give you any more information please let me know.

    Thanks very much,

    Rachel

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your problem here is that Iif is an expression NOT a statement.
    This link covers exactly this point (except it refers to SQL Server's CASE expression, which is broadly equivalent to Access's Iif):
    In SQL, it's a Case Expression, *not* a Case Statement

    You are effectively getting the user to define part of the SQL Statement (i.e. the comparison operator). I would avoid this if you can, or hard code a few queries. If you still want to use this construction you can use:
    Code:
    WHERE IIf([Forms]![Filter Actions Log]![Review BA combo]="Before" AND myDate<[Forms]![Filter Actions Log]![Review Date], 1, IIf([Forms]![Filter Actions Log]![Review BA combo]="After" AND myDate >[Forms]![Filter Actions Log]![Review Date], 1, 0)) = 1
    Last edited by pootle flump; 04-08-09 at 05:38.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, a more readable and optimised version would be:
    Code:
    WHERE (([Forms]![Filter Actions Log]![Review BA combo]="Before" AND myDate<[Forms]![Filter Actions Log]![Review Date])
    OR
    ([Forms]![Filter Actions Log]![Review BA combo]="After" AND myDate >[Forms]![Filter Actions Log]![Review Date]))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2009
    Posts
    17
    Hi,

    Thanks very much for your help. I’m afraid I couldn’t get your suggestions to work – I put them in query design view in my ‘Review Date’ criteria, is that right? The first gave me ‘invalid syntax’, and the 2nd gave me an ’undefined where function’.

    I was thinking about your suggestion of having several queries, and I thought this might be better. Could I have an if statement in my code for the submit button, as in (just the logic, not the syntax!):

    IF
    Combobox=’Before’ THEN load query 1
    ELSE load query 2

    Would this work? If so, what would be the code for getting the value from the combobox?

    Please let me know if there’s another better way to do what I want!

    Thanks very much,

    Rachel

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I believe the intention is that you go to your query, switch to SQL View and then replace your WHERE clause with the ones posted.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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