nested IIF problem (Access 97)

Topic

New Reply

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?

Reply | Quote

Replies

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?????????/

Reply | Quote

I gather that the expression you posted is to act as criteria in a query.

• On what kind of field? Text or numeric?
• 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. “n Or up” as a literal string, or
  2. “17 or Up” as a literal string, or
  3. >=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)
  4. Something else entirely – if so, what? (Is up a field?)

  [/list]Please explain.

Reply | Quote

[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

Reply | Quote

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"?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote