If...then...else statement 1

[amrnewman]

Hi. Am new to Access and do not know it very well - am much more comfortable with Crystal Reports! I am sure that this is a VERY basic request, but I am lost. I have searched through other posts but cannot really find what I am looking for. Am using A2K and need to build an expression. All fields are in the same table.

I want to sum some numeric fields based on a YES/NO check box. If the response is YES then add on an additional cost otherwise just sum the main financial years.

In Crystal I would have written similar too:

if [Add-on] = YES then ([FY!]+[FY2]+...[FY9]+[Add_On_Cost]) else ([FY!]+[FY2]+...[FY9])

But this is not working for me! Have tried substituting YES for IsNotNull etc etc.

Please could someone help me write the correct syntax!!!!

Thank in advance, Ade

 

[Remou]

True or simply Add-On with nothing should work:

If [Add-On]

If [Add-On]=True

 

[amrnewman]

Thanks Remou but no joy! The expression looks like this:

Expr3: If( [Add on]=TRUE) then ( [1st FY]+[2nd FY]+[3rd FY]+[4th FY]+[5th FY]+[6th FY]+[7th FY]+[Extension cost]+[Add on cost]) else [Expr2]

[Expr 2]is the sum of the fields without the add on cost. The message I am getting is '...you may have added an operand without an operator' and pause at the then statement.

Help - am sure this must be really simple, but I'm tieing myself in knots with it!!!!

Ade

 

[Golom]

Try

Expr3: [1st FY]+[2nd FY]+[3rd FY]+[4th FY]+[5th FY]+[6th FY]+[7th FY]+[Extension cost]+IIf([Add on],[Add on cost],0)

SQL doesn't support the "If ... Then ... Else" syntax. You need to use an immediate or in-line IIF statement.

 

[amrnewman]

Thanks Golom. I used

Expr3: IIf([Add on]=Yes,([Expr2]+[Add on cost]),[Expr2]) in the end.

managed to figure out a bit more of access!

Ade

 

[Golom]

Or even simpler

Expr3: [Expr2] + IIf([Add on],[Add on cost],0)