If then statements

[sshafer1]

I am an avid user of Crystal Reports. I'm in a new job that is requiring me to build an access database. I know that I can write a formula in Crystal to accomplish what I am looking for, however, I would prefer this data to be stored in a table.

This is how my formula is written in Crystal: I would like this to be automatically calculated based on data that is gathered using a form and then stored in a table. I am beside myself trying to figure this out.

Any suggestions?

If {Calculations.Business Impact Vector}<=14 AND {Calculations.Total Spend CY}<=2 Then "Tier 4"
Else

If {Calculations.Business Impact Vector}<=21 AND {Calculations.Total Spend CY}<=10 Then "Tier 3"
Else

If {Calculations.Business Impact Vector}<=28 AND {Calculations.Total Spend CY}<=100 Then "Tier 2"
Else

If {Calculations.Business Impact Vector}=28 AND {Calculations.Total Spend CY}>100 Then "Tier 1"

[MMcCarthy]

Assuming Calculations is a table and Business Impact Vector and Total Spend CY are fields in that table then you can design a query as follows:

SELECT 
IIf([Business Impact Vector]<=14 AND [Total Spend CY] <=2, "Tier 4",
IIf([Business Impact Vector]<=21 AND [Total Spend CY] <=10, "Tier 3",
IIf([Business Impact Vector]<=28 AND [Total Spend CY] <=100, "Tier 2",
IIf([Business Impact Vector]=28 AND [Total Spend CY] >10, "Tier 1"))))
FROM Calculations
 

However, I think you have a problem with your logic. If [Business Impact Vector] =10 and [Total Spend CY]=2 then this will hold true for all of the first three conditions.

The syntax for an If statement is ...

IIF(<Condition>,<value if true>,<value if false>)

[sshafer1]

Thanks! One more question...where do I add this expression? I dropped an unbound text field to my form and added the code below to the 'Expression Builder' in the Control Source property. When I view the form, the following populates the text box '#Name?'.

1. How do I correct this so that I can see the 'Tier #' in the box?
2. How do I set this up to write to a table?

Once I have this, I can re-use it for a bunch of other things I'm working on.

IIf([Business Impact Vector]<=14 AND [Total Spend CY] <=1000000, "Tier 4",
IIf([Business Impact Vector]<=21 AND [Total Spend CY] <=2000000, "Tier 3",
IIf([Business Impact Vector]<=28 AND [Total Spend CY] <=5000000, "Tier 2",
IIf([Business Impact Vector]=28 AND [Total Spend CY] >5000000, "Tier 1"))))

Thanks!

[MMcCarthy]

Thanks! One more question...where do I add this expression? I dropped an unbound text field to my form and added the code below to the 'Expression Builder' in the Control Source property. When I view the form, the following populates the text box '#Name?'.

1. How do I correct this so that I can see the 'Tier #' in the box?
2. How do I set this up to write to a table?

Once I have this, I can re-use it for a bunch of other things I'm working on.

IIf([Business Impact Vector]<=14 AND [Total Spend CY] <=1000000, "Tier 4",
IIf([Business Impact Vector]<=21 AND [Total Spend CY] <=2000000, "Tier 3",
IIf([Business Impact Vector]<=28 AND [Total Spend CY] <=5000000, "Tier 2",
IIf([Business Impact Vector]=28 AND [Total Spend CY] >5000000, "Tier 1"))))

Thanks!

The two fields [Business Impact Vector] and [Total Spend CY] have to be in bound controls on the form also and make sure the controls have the same name.

[sshafer1]

Ok. That helps. I added the IIF statement as a Query field and defined the query field value as TierEx: then put the IIF statement in. Doing this at least generated the correct value that I was looking for.

However, Calculated is really a query not a table. I am calculating [Business Impact Vector] and [Total Spend CY] in a query because they are adding multiple question responses together from a form.

How can I write the calculated values from the query to a table?

Your help is greatly appreciated!