Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF expression (2000)

    This is an IIF expression i have in a query, its returning an error, been playing with it for a couple of hours, and i cannot figure out why..

    <font color=blue>TEST: IIf([discounted]="Yes" And ([datelc]<[Forms]![Switchboard]![Begin_Date]),1,0)</font color=blue>

    It is actually putting #error in the query for every record. I've set it up as an expression.

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

    Re: IIF expression (2000)

    Steve,

    I assume this is the same database that featured in your previous thread. In <post#=238235>post 238235</post#> you wrote
    <hr>[discounted] which i have as a string, is actually a YES/NO type of field in the table.<hr>
    If we're dealing with the same [discounted] field here, you should use [discounted]=True (without quotes) instead of [discounted]="Yes".

    I can be even shorter: since [discounted] is a boolean, [discounted] by itself is equivalent to [discounted]=True.

    And for most practical purposes, you could use this:

    TEST:[discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date]))

    This will return True (-1) if both conditions are fulfilled, False (0) otherwise. It is more efficient since it avoids IIf. But there *is* a difference: if [datelc] is null (empty), the IIf expression will return 0, but the expression without IIf will return Null.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIF expression (2000)

    In addition to what Hans pointed out, if datec is a date field and the Begin_Date control on your form is unbound, you will probably need to wrap the form reference in a CDate function in order to compare apples to apples.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF expression (2000)

    Yes Hans, same database... (the bane of my life at the moment!) <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I need it to return a value, so i can't use <font color=blue>TEST:[discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date])</font color=blue>

    i did trying using <font color=blue>TEST: nz([discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date]))</font color=blue>

    but that still returns an occasional null field.

    so using IIF , - i used, <font color=magenta>TEST: IIf([discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date]),True,False)</font color=magenta> which works

    Key Learnings:
    <font color=blue>My beer total i owe Hans, is now > <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Access is fussy about what and how you use things, using 0, and 1, gave me an error in the field, yet using True and False, works ok.Unlike a lot of other software programs (Excel for example) Access is very unfriendly with regard to the number of brackets required in a field.. so for instance, in Excel it will suggest an alternative. Access won't do this it will just tell you your text is no good and to fix it.
    Even experts put in the wrong number of brackets !!! (see above)
    </font color=blue>

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

    Re: IIF expression (2000)

    Steve,

    Glad you worked it out yourself. Sorry about the extra bracket <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    This would probably work too:

    TEST: Nz([discounted] And ([datelc]<[Forms]![Switchboard]![Begin_Date]),False)

    This states that you want to return False if the first argument is Null.

Posting Permissions

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