Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OR expression (2003)

    I need an expression that says:

    Terms of Payment: IIf([Yes/No field]=-1,"Due Upon Receipt","Net 30 Days") OR IIF([DateofService]=>1/1/2009 and [yes/no field]=2,"One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made")

    My responses to this expression have been all over the place but most of the time I get a message saying I'm missing a closing bracket or paren - but even after adding those I get errors.

    Can you help?

    Thanks

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

    Re: OR expression (2003)

    A yes/no field is either True = -1 or False = 0. How can it be 2?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: OR expression (2003)

    Is [Yes/No field] the actual name of your field? If so, that may be causing you difficulties - Yes and No are internal constants that Access uses. In addition the use of the "/" character is also a possible problem - it usually means division and you would thus be dividing -1 by 0 - which is indeterminate. Also, is your field really defined as a "Yes/No" field in the table, or is it some sort of numeric table such as an Integer?
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    Sorry, that was a typo; in actuality, I used 1 and 0

    Sorry!

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    Sorry, I didn't express all of this very well, I used 1 and 0 for yes and no in the actual formula; what I used in the post was just to clarify the question so you would know what I was trying to do. So, in fact, I've only used 1 and 0 and not Y or Yes and No so the fact that it does't work is something other than that and as I work with it I keep thinking it's joinng two expressions with OR that is causing the problem?

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

    Re: OR expression (2003)

    The logic of what you want is not clear. Could you indicate what you want to return in each of the four situations (1)...(4)?

    <table border=1><td valign=top></td><td valign=top></td><td valign=top>Yes/No field</td><td valign=top></td><td valign=top></td><td valign=top></td><td valign=top>Yes</td><td valign=top>No </td><td valign=top>DateOfService > 1/1/2009</td><td valign=top>Yes</td><td valign=top>(1)</td><td valign=top>(2)</td><td valign=top></td><td valign=top>No</td><td valign=top>(3)</td><td valign=top>(4)</td></table>
    Thank you.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    I need an expression that says:

    Terms of Payment: If the Yes/No field is True,[Terms of Payment] should return: "Due Upon Receipt", but if the [DateofService] is on or after1/1/2009 AND the Yes/No field is False, the [Terms of Payment] should return: "One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made"

    I hope that is more clear.

    Thanks.

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

    Re: OR expression (2003)

    Please answer my previous question. Thank you.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: OR expression (2003)

    OK - with your following posts, I think I'm beginning to understand, and I think the issue is with your OR.

    I assume you are doing this is a query or a SQL string that is being built in VBA since the IIf is normally only used that way. In that case, you cannot use an OR the way you have constructed it. IIF statements have to be constructed as:
    IIf([booleanfield],""Due Upon Receipt",IIf([DateOfService]>=#1/1/2009#,"One Half....","Due Upon Receipt")

    However Han's question still stands, as your verbal description doesn't cover all four cases. In fact your description is inconsistent if you intended that there be two different cases when the [booleanfield] is True.

    A final question - what is the real name of your [booleanfield]?
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    Actually I thought I had. I'll try again.

    HERE'S THE FIRST PART:

    Terms of Payment: IIf([MemSociety]=-1,"Due Upon Receipt",Net 30 Days")

    HERE'S THE SECOND PART:

    IIF([Date]=>1/1/2009 and [MemSociety]=0,"One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made")

    The above is actually the expression as I typed it in the query for field TERMS OF PAYMENT. I need to join those two expressions together and I was using OR. Maybe that's not the way to go, but I hope above at least explains it better.

    Thanks again

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

    Re: OR expression (2003)

    I think I misinterpreted your intention. If you want to concatenate the expressions, use &

    Terms of Payment: IIf([MemSociety]=-1,"Due Upon Receipt.","Net 30 Days.") & IIf([Date]=>1/1/2009 and [MemSociety]=0," One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made","")

    Note the extra quote before Net 30 Days, the space before One Half Balance, and the empty string "" as second argument in the second IIf.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    I can't understand using &, Hans. This has to be either or. So if MemSociety is true, the statement would read one way, but only if it is false, should it read, "One Half Balance....."When I use the expression you send me it actually prints both, i.e., "Net 30 Days.One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made"

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

    Re: OR expression (2003)

    That's because you haven't provided complete information yet. Could you please answer <post#=752,820>post 752,820</post#>?

  14. #14
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OR expression (2003)

    Actually, this worked just fine:

    Terms of Payment: IIf([MemSociety]=-1,"Due Upon Receipt.","Net 30 Days.") & IIf([Date]=>1/1/2009 and [MemSociety]=0," One Half Balance is Due upon Completion of Service and Balance Within 30 days of Invoice Date, Unless Other Arrangements Have Been Made","")

    Sorry to be so long in responding, and for the fractured stuff last week; came down with something that didn't want to let go! I'm all set and appreciate the help, Hans.

Posting Permissions

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