Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    nested IIF problem (Access 97)

    My code in a query is :
    IIf([forms]![frmm]![n] Mod 2=1,IIf([forms]![frmm]![first]<[forms]![frmm]![last],"n Or up","s Or up"),IIf([forms]![frmm]![first]<[forms]![frmm]![last],"e Or up","w Or up"))

    If n is odd and first is less than last I want the critria in the query to be n Or up,
    but if n is odd and first is greater than last then I want the critria to be s Or up,
    If n is even and first is less than last I want the critria in the query to be e Or up,
    but if n is even and first is greater than last then I want the critria to be w Or up,

    I get back nothing or nulls as my answer when there should be records?
    does the statement above is it putting n Or up as the critria? because I believe the odd and even is working right?

  2. #2
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IIF problem (Access 97)

    when I take out the double quoates "n Or up" access makes it "n" Or "up"..............and when I ran that query it comes back with
    the error expression type incorrectly or too complex?????????/

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

    Re: nested IIF problem (Access 97)

    I gather that the expression you posted is to act as criteria in a query.
    <UL><LI>On what kind of field? Text or numeric?
    <LI>What do you mean by "n Or up"? n seems to be a numeric value; say that n =17, do you want the criteria to be
    1. <LI>"n Or up" as a literal string, or
      <LI>"17 or Up" as a literal string, or
      <LI>>=17 as a numeric criteria (your browser may not display "greater than or equal" before 17 correctly, but it should be read as greater than or equal to 17)
      <LI>Something else entirely - if so, what? (Is up a field?)
    [/list]Please explain.

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IIF problem (Access 97)

    [n] is a number.......but when I go n or up ......the n and up is text
    the [n] is a road number........so depending on if the road is odd or even then I need to look for a direction, so the critria will be one of four.......odd number road ........n or up
    s or up
    even number road e or up
    w or up

    the up is text but has a meaning(other than up) and is looking for values of up.....just as the n should be looking for values of n

    [first] and [last] are numbers miles post number which will indicate a direction also

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

    Re: nested IIF problem (Access 97)

    I'm still not sure that I understand. Is up the name of a field in the query? In that case, you might try

    IIf([forms]![frmm]![n] Mod 2=1,IIf([forms]![frmm]![first]<[forms]![frmm]![last],"n Or " & [up],"s Or " & [up]),IIf([forms]![frmm]![first]<[forms]![frmm]![last],"e Or " & [up],"w Or " & [up]))

    Or do you want to replace "up" in a series of nested IIf's just like you do for "n"? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IIF problem (Access 97)

    up is not a name of a field but is text just like...n,s,e,w ,that I have to look for .....
    let me add that I need ..............n Or up to go into the critria can I place that whole Iff statement in the
    critria area and depending on the Iff it will put n Or up, s Or up, e Or up, w Or up in the blank for me?

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

    Re: nested IIF problem (Access 97)

    If Or is meant as the SQL operator, I don't think you can construct criteria this way (with IIf). You'll probably have to write out the different combinations in the query grid. See attached picture ("Waar" = True and "Onwaar" = False in Dutch). The SQL for the criteria would look like

    ((([forms]![frmm]![n] Mod 2)=1) AND (([forms]![frmm]![first]<[forms]![frmm]![last])=True) AND ((tblTest.Zip)="n" Or (tblTest.Zip)="up")) OR ((([forms]![frmm]![n] Mod 2)=1) AND (([forms]![frmm]![first]<[forms]![frmm]![last])=False) AND ((tblTest.Zip)="s" Or (tblTest.Zip)="up")) OR ((([forms]![frmm]![n] Mod 2)=0) AND (([forms]![frmm]![first]<[forms]![frmm]![last])=True) AND ((tblTest.Zip)="e" Or (tblTest.Zip)="up")) OR ((([forms]![frmm]![n] Mod 2)=0) AND (([forms]![frmm]![first]<[forms]![frmm]![last])=False) AND ((tblTest.Zip)="w" Or (tblTest.Zip)="up"))

    (I have used a fictitious table tblTest with a field named Zip for illustration purposes)

    Otherwise, I wouldn't know <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Attached Images Attached Images
    • File Type: png x.png (2.1 KB, 0 views)

  8. #8
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IIF problem (Access 97)

    HansV,
    Thanks for helping me, I can get my Iff Statement to work in the query if I take out the "Or up"....I can get the n,s,e and w....
    records...I am going to try and work with it for a while...your last post is what I am trying
    if you come up with a way to add the "Or" statement with an "up" let me know....if I can not get it to work then I will
    try your solution for me...Thanks again.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: nested IIF problem (Access 97)

    Sorry for jumping in here, but do you want the expression to evaluate to a value of 1 of the following:
    "n Or up"
    "s Or up"
    "e Or up"
    "w Or up" ?

    Does your select need to look like:
    SELECT ........., IIf([forms]![frmm]![n] Mod 2=1,IIf([forms]![frmm]![first]<[forms]![frmm]![last],"n Or up","s Or up"),IIf([forms]![frmm]![first]<[forms]![frmm]![last],"e Or up","w Or up")) as FieldEvaluated
    FROM Tablename

    In this case the above query should work, unless I have misunderstood your question.
    Why don't you post the SQL of your query so we can all have a look.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IIF problem (Access 97)

    Thanks Patt for jumping in,
    I think I final came up with a solution, and would like anyone's oppion,
    I think I should do below in the query:

    =UP
    = IIf([number] Mod 2=1,IIf([forms]![frmm]![First]<[forms]![frmm]![Last],"NBL","SBL"),IIf([forms]![frmm]![First]<[forms]![frmm]![Last],"EBL","WBL")

    UP can be with any of the four choices of NBL,SBL,WBL,EBL
    Friday I just got stuck into thinking that I needed UP in the IFF statement but I think I can get the results I am looking
    for by pulling UP out by itself,
    Does this help you understand what I am trying to get?
    I won't be able to test this until tuesday.

Posting Permissions

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