if statement

[Dona]

I'm new at this and need help.

I want to use the income field and dependent field to determine if a person fits the criteria.

Income Dependents
25500 or less 2
32000 or less 3
40000 of less 4

I tried the following:

Iif [Income]<=25000 and [Dependents]=2 or [Income]<=32000 and [Dependents]=3 or [Income]<=40000 and [Dependents]=4, “Yes”,) “No”)

Thank you in advance for your time.

 

[Brianwarnock]

You need some( ) Try
IIf(([Income]<=25000 and [Dependents]=2) or ([Income]<=32000 and [Dependents]=3) or ([Income]<=40000 and [Dependents]=4), “Yes”, “No”)

Brian

 

[Dona]

Hi Brian,

When I tried to run the query, a dialog box pops up and says:
Enter Paramenter
"Yes"

and another box for "No"

Why?

Dona

 

[Brianwarnock]

If it is written as I posted I don't know, it appears to think that they are parameters, for that to happen you have to write ["yes"]

Brian

 

[Brianwarnock]

OK pasted the expression into a query grid and [] were inserted around "yes" ans "no", so deleted and retyped and it was ok, so suggest you do the same.

Brian

 

[Dona]

Thank you. I needed to retype it several times for it to work.
I have another question.
I forgot to add if
40000 of less 4
For each addition dependent add 2000
How would I write this??

([Income]<=40000+2000x and [Dependents]=4+X)


IIf(([Income]<=25000 and [Dependents]=2) or ([Income]<=32000 and [Dependents]=3) or ([Income]<=40000 and [Dependents]=4) or ([Income]<=40000+2000x and [Dependents]=4+X), “Yes”, “No”)

 

[Brianwarnock]

No Dona that wont work, but I cannot thing of a simple way to do that. Is there an upper limit? Are we lookihg at some kind of lookup table or will it be a function. I don't know off hand.

Brian

 

[Dona]

If would be part of the function. There is no limit.

 

[WayneRyan]

Dona,

Add a new column to your query and call your "NEW" Public Function:

QualificationStatus: Qualifies([Income], [Dependents])

Then add this code to a module.

Public Function Qualifies(Income As Double, Dependents As Long) As String

If (Income <= 25000 and Dependents = 2) Or _  <-- I think these "=" signs
   (Income <= 32000 and Dependents = 3) Or _      should be ">" signs
   (Income <= 40000 and Dependents = 4) Or _
   (Dependents > 4 And _
    Income > 40000 And _
    Income <= 40000 + 2000 * (Dependents - 4)) Then
  Qualifies = "Yes"
Else
  Qualifies = "No"
End If

End Function

It'll be much easier to straighten out your logic in a few lines of code
as opposed to a multi-level IIf statement.

hth,
Wayne

 

[Dona]

Hi WayneRyan,

I don't think I'm using the module correctly. An error message was returned.
I did the following:

I created a module and named it QualificationStatus. I typed QualificationStatus: Qualifies([Income], [Dependents]) in the query field.

What did I do wrong?

Thank you

Dona

 

[CraigDolphin]

Or you could try the same logic without the vba function

QualificationStatus: IIF([Income]<Switch(Dependents < 2,25000,Dependents=3,32000,Dependents=4,40000,Dependents>4,40000+(2000*(Dependents-4))),"Yes","No")

 

[Brianwarnock]

I agree with Wayne about using code rather than multilayer IIF, but cannot see what you did wrong. I'm not happy with the = [Dependents], I think that it should be like this if you wish to use IIF.


IIf(([Income]<=25000 And [Dependents]>=2) Or ([Income]<=32000 And [Dependents]>=3) Or ([income]-40000)/2000<=([dependents]-4),"Yes","No")

Brian

Edit decided to try Waynes and after removing comment, and I changed the comparitors to >= for dependents, it worked fine. But discovered a flaw in my IIF !

EDIT 2 ok corrected it, but it now illustrates why the code is simpler, of course the Switch might work but couldn't figure it.

IIf(([Income]<=25000 And [Dependents]>=2) Or ([Income]<=32000 And [Dependents]>=3),"yes",IIf([income]>=40000,IIf(([income]-40000)/2000<=([dependents]-4),"Yes","No"),"NO"))

 

[Brianwarnock]

Switch works with minor alteration

IIf([Income]<=Switch([Dependents]=2,25000,[Dependents]=3,32000,[Dependents]=4,40000,[Dependents]>4,40000+(2000*([Dependents]-4))),"Yes","No")

Brian

I lead a sad life.!!

 

[CraigDolphin]

Oh, I guess I misread the '= <value> or less than' bit of the first post. But the rest of the logic works fine and ever since I learned about switch I've found it much easier to read.

I've always preferred to use built in functions like iif and switch, rather than coding a new module, since I have heard that you get less of a performance hit with large datasets. Of course, I am likely talking through my hat on that

 

[Brianwarnock]

Oh, I guess I misread the '= <value> or less than' bit of the first post. But the rest of the logic works fine and ever since I learned about switch I've found it much easier to read.

I've always preferred to use built in functions like iif and switch, rather than coding a new module, since I have heard that you get less of a performance hit with large datasets. Of course, I am likely talking through my hat on that

I also believe that you are correct about built in functions v own code performance, mainly because Pat Hartman said so , well actually entirely because she said so.. However sometimes maintainability wins over performance.

As to the switch it seems to have arrived after my retirement, so I'm only just coming to terms with its potential, I enjoyed wrestling with your solution and it is the one I would choose whilst recognising that the code is simple to follow.

Brian

 

[CraigDolphin]

I enjoyed wrestling with your solution

Glad to contribute some brain-fodder then And yes, I agree the code is much easier to read.

 

[WayneRyan]

Brian & Craig,

I admire your persistence with the IIf and Switch versions of this exercise.

I generally will migrate to a Public function once the IIf gets to more than
2 levels.

Dona, you didn't say what your error was. It doesn't matter what you
name the module, but the function name must be the same as in the
query.

In practice, I don't feel a performance hit with a VBA function as opposed
to a Built-In function. Even with large tables.

Wayne