IIF([Or/And/Else]) (1 Viewer)

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
Hi all,

I'm trying to work out a query in design view, I'm using the IIF statement and was wodering if there's a way to use an else type function?

This is what I have working:

IIF([field1]=Y And [field2] Between 1 And 5,"Level1,"Decline")

This is where I'm trying to get to:

IIF([field1]=Y And [field2] Between 1 And 5,"Level1","Decline" Else [field2] Between 6 And 10,"Level2" Else [field2] Between 11 And 15, "Level3")

I've tried various options but none work, and just in case the above doesn't make sense this is what I'm trying to do.

field1 has to be Y otherwise it's a straight decline and no offers given
If field1 has Y then the query will test the value in field2 and depending on the value, will display the correct info (for this example Level1/2/3 etc)

I don't know SQL so is it possible to achieve this in the normal design view using the IIF statement?

Thanks in advance
 

Mile-O

Back once again...
Local time
Today, 12:06
Joined
Dec 10, 2002
Messages
11,316
In this sort of situation you are better to use VBA as the IIF() function (especially multiple occurrences of it) can slow down a query due to its method of evaluation.

So, a VBA solution, to be put inside a new module:

Code:
Public Function QueryValues(ByVal fld1 As String, ByVal fld2 As Long) As String
    On Error Goto Err_QueryValues
    If fld1 = "Y" Then
        Select Case fld2
            Case <= 0
                 QueryValues = "Decline"
            Case <= 5 
                 QueryValues = "Level1"
            Case <= 10
                 QueryValues = "Level2"
            Case <= 15
                 QueryValues = "Level3"
            Case Else
                QueryValues = "Decline"
        End Select
    Else
        QueryValues = "Decline"
    End If
    Exit Function

Err_QueryValues:
    QueryValues = "Decline"
End Function

In your query:

NewField: QueryValues([Field1], [Field2])


The only thing is that you have another thread where you have mentioned this already and it is not the way to go - you should not have to hard code these values into a query or VBA at all. As I said on the other thread, use a new table for storing the limits and values of offers.
 

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
Don't know if this will help, but you can embed iif() statements. Below are some examples:

Code:
iif(condition, truepart, falsepart)

iif(condition, truepart, iif(condition, truepart, falsepart))

iif(condition, truepart, iif(condition, truepart, iif(condition, truepart, falsepart)))

Ken
 

Mile-O

Back once again...
Local time
Today, 12:06
Joined
Dec 10, 2002
Messages
11,316
KenHigg said:
you can embed iif() statements.

Ken, the problem with IIF() expressions are that each side of them (true and false) is evaluated and then the correct one chosen.

So, in IIf(x=5,"YAY!","NAY!") where x=0 goes is x = 5 and then it goes is not equal to 5 before chosing the correct true or false part to return. Double the time taken to do an evaluation. When you start embedding the IIf() expressions the evaluation time increases per record.
 

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
Stew, I understand that. But when you only have a few records, performance isn't that big of an issue. The only time I would recommend embedded iif()'s is in adhoc reports, etc.

I agree, in production db's embedded iif()s are bad juju. Not only for the performance issue you pointed out, but I like the solution you posted because it gets business rules out into a mod which is easier to manage than having it tucked away in some obscure query. Seems this particular set of rules may be required in a report somewhere down the line. Having it in a function will allow the logic to be used over and over with out having to re-create the wheel every time...

FYI - You posted just before I did. I didn't know you were replying. :)

Ken
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
Thanks for the speedy replies guys...

I'm going to play with both soloutions, I understand what is happening more with the IIF option over the VBA and for this purpose this might be fine, however as the VBA option is better overall this will be more important (and I need to get into VBA at some point)

Will let you know how I get on and sure I'll have more Q's ;-)
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
I thought I was getting there...The part in the query where the value is Y works but when I have a text value such as name1 the query doesn't work and puts square brackets round it?
 

Mile-O

Back once again...
Local time
Today, 12:06
Joined
Dec 10, 2002
Messages
11,316
Put text within quotes.
 

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
I didn't try this but plug it in and see if it works:

Code:
IIF(([field1]=Y) And ([field2] Between 1 And 5),"Level1", IIF(([field1]=Y) And ([field2] Between 6 And 10),"Level2", IIF(([field1]=Y) And ([field2] Between 11 And 15),"Level3", "Decline")))


ken
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
still working on this and the IIF (and embeded IIF) works fine for a couple of queries but the last query is 'to complex' to run so I'm guessing that I'm trying to use to many IIF's??

This is what I was trying to get to work (all fields being pulled from another query that also uses the IIF embeded in one of the query fields.
IIF([RiskBand]= "RB5" Or "RB4" And [Income] Between 15 And 32,"7000", IIF([RiskBand]= "RB5" Or "RB4" And [Income]= 40,"8500", IIF([RiskBand]= "RB5" Or "RB4" And [Income]= 50,"10500", IIF([RiskBand]= "RB5" Or "RB4" And [Income]= 60,"12500", IIF([RiskBand]= "RB5" Or "RB4" And [Income]= 65,"13500", IIF([RiskBand]= "RB3" And [Income] Between 15 And 25,"9000", IIF([RiskBand]= "RB3" And [Income]= 32,"9500", IIF([RiskBand]= "RB3" And [Income]= 40,"11500", IIF([RiskBand]= "RB3" And [Income]= 50,"14500", IIF([RiskBand]= "RB3" And [Income] Between 60 And 65,"16000", IIF([RiskBand]= "RB2" Or "RB1" And [Income] Between 15 And 17,"9000", IIF([RiskBand]= "RB2" Or "RB1" And [Income] Between 25 And 32,"13500", IIF([RiskBand]= "RB2" Or "RB1" And [Income]=40,"16500", IIF([RiskBand]= "RB2" Or "RB1" And [Income] Between 60 And 65,"20000","Declined"))))))))))))))
I'm going to start with the VBA solution, if anyone can give advice on this, much appreciated.
 

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
I think I would do this in a fuction, kind of like the one Mac posted.

Ken
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
fell at the first jump...(working with the above VB option)

I've never worked in VB before so please bare with me,

Could you explain the first line if I'm getting it wrong?

QueryValues: Name of this Public Function
ByVal fld1 As String: Field One is a text field
ByVal fld2 As Long: Field Two is a Long Integer
As String On Error GoTo Err_QueryValues: This points to the error text at the end of the code

Couple of questions here:
Why is part of the first line in brackets?
Where do I specify what table to use?
If I wanted to add more fields would these go in the brackets as well?
 

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
I would suggest you do a little easier function to see how they work...

Otherwise:

1. We can leave the 'ByVal' out for the purpose of this drill.

Code:
Public Function QueryValues(ByVal fld1 As String, ByVal fld2 As Long) As String
So we now have:

Code:
Public Function QueryValues(fld1 As String, fld2 As Long) As String

2. fld1 and fld2 are variables that will contain the values you send the function.

3. So when you use the function, it may look like:

QueryValues("Some string", 50)

So that further down in the function, you use the variable 'fld1', which will contain "Some string" and 'fld2', which will contain 50.


Maybe this helps a little?
Ken
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
ok answered the question re: Where do I specify the table

I realise now that this is done in the query in the normal way.

Another question: How do I specify Between to values in the code? I've tried:

Case Is Between 100 And 200 but I get prompted for a = or <> etc but when I do I get a Compile Error: Expected: end Statement??
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
Hi Ken,
Basically it the same as what SJ posted with modifications to the field names...

Option Compare Database
----------------------------------------------------
Public Function QueryValues(ByVal ValuedCust As String, ByVal Spend As Long) As String
On Error GoTo Err_QueryValues
If Employed = True Then
Select Case Spend
Case Is <= 100
QueryValues = "100"
Case Is <= 200
QueryValues = "199"
Case Is > 400
QueryValues = "400"
Case Is > 500
QueryValues = "500"
Case Else
QueryValues = "Decline"
End Select
Else
QueryValues = "Decline"
End If
Exit Function

Err_QueryValues:
QueryValues = "Decline"
End Function
----------------------------------------------------
I was hoping to have a between check in there but is this only for queries / sql?
 

Mile-O

Back once again...
Local time
Today, 12:06
Joined
Dec 10, 2002
Messages
11,316
Just use the function as I gave it. It works exactly as you specified and there's no need to change any wording.

There is no need for Between...And syntax (and there's no Between keyword in VBA anyway) as the code goes through only the steps that it needs to. Thus if the value is less than 100 it goes to that result, if not it goes to if the value is less than 200. If it is less than 200 then it must be between 100 and 200 as we have already eliminated the fact that it is less than 100. ;)

And don't put numeric values in inverted quotes - this makes them strings. Consider the order when sorting string numbers against literal numbers.

i.e.
Numbers as a string
1
10
11
12
13
14
15
2
20
21
3

Numbers as literal numbers
1
2
3
10
11
12
13
14
15
20
21

So, consider how your greater than and less than evalutions work on textual numbers rather than literal numbers. With text, 10 is less than 2. ;)
 
Last edited:

KenHigg

Registered User
Local time
Today, 08:06
Joined
Jun 9, 2004
Messages
13,327
SJ McAbney said:
Just use the function as I gave it. It works exactly as you specified and there's no need to change any wording.

There is no need for Between...And syntax (and there's no Between keyword in VBA anyway) as the code goes through only the steps that it needs to. Thus if the value is less than 100 it goes to that result, if not it goes to if the value is less than 200. If it is less than 200 then it must be between 100 and 200 as we have already eliminated the fact that it is less than 100. ;)

Thanks for bailing me outta this one - :p

Ken
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
nice one guys, I am coming to the same conclusions albeit a little slower ;-) just need confirmation on my understanding so I don't go mad. Will keep working on it.
 

Tezdread

Registered User.
Local time
Today, 12:06
Joined
Jul 11, 2002
Messages
105
going going gone mad!

Sorry guys I still need help with this if you can...

I have been testing and have managed to expand my code to this:

Code:
Public Function QueryValues(ByVal Employed As String, ByVal SpendScore As Long, ByVal NumPurchases As Long) As String
    On Error GoTo Err_QueryValues
    If Employed = True Then
        Select Case SpendScore
            Case Is <= 100
                 QueryValues = "Is 100"
            Case Is <= 200
                 QueryValues = "Is Lower than 200"
            Case Is > 400
                 QueryValues = "Is Greater than 400"
            Case Else
                QueryValues = "Decline"
        End Select
                  Else
        QueryValues = "Decline"
    End If
    If Employed = True Then
        Select Case NumPurchases
            Case Is > 0
                Case Else
                QueryValues = "Decline"
        End Select
                    Else
        QueryValues = "Decline"
    End If
    Exit Function

Err_QueryValues:
    QueryValues = "Decline"
End Function

This works but am I doing it right? The second "If Employed = True Then" was only added because I couldn't get it to work without it but it's not needed as it's already in the first part...

I have a lot more that needs to be added to this query so need the best way to do it
 

Users who are viewing this thread

Top Bottom