Having Syntax Trouble with Formula

[Cheryl3D]

Hi:

I am new to Crystal Decisions' Crystal Reports. I am using version 8.5 and I am having problems with formulas. This is my first formula.

I had a complex report in Access 2000 that used the following nested immediate IF statement for a calclaued field on the report:

=(IIf([RCost]>0,[RDescription],(IIf([NRCost]>0,[NRDescription],(IIf([NRManpower] Is Not Null,"Manpower Workyear Cost"))))))

RCost, NRCost, NRManpower is a number (real)datatype field, and RDescription is a text(nvarchar) field.


I searched Crystal Report's help menu and found out that I can use the same IIF function as in Access 2000,
IIF(expression, truePart, falsePart).

I'm am using SQL Server 2000 with Crystal Reports, not Access 2000. (Just converting the Access 2000 database).

I took the same nested IIF statement mentioned above, and tried to convert it to a formula using Crystal Report's syntax in the Record Selectin Formula Editor, see the following:

IIF({qryAnnex_View.RCost}>0,{qryAnnex_View.RDescription},IIf({qryAnnex_View.NRCost}>0,{qryAnnex_View.NRDescription},(IIf(Not IsNull({qryAnnex_View.NRManpower}),"Manpower Workyear Cost"))))

When I tried to validate this formula (Check: Alt+C), I received the following error message:

Not enough arguments have been given to this function.

What am I doing wrong? I'm only trying to convert the nested IIf statement in Access to a formula in Crystal Reports.

Could I do it better using a If..Else formula statement?

If so, how?

Thanks,
Cheryl3D

 

[synapsevampire]

I tend to be kind to the next developer and avoid complex IIFs.

Your IIF has a few things wrong with it.

For one thing, you return numerics in most of this, but then use "Manpower Workyear Cost" for one condition, which isn't allowed, you need to return the same data type within a formula. Your original Access formula didn't do this, so it has nothing to do with differences in CR, you've placed something entirely different in there.

Next I would qualify all NOT statements with parentheticals ()

Next you've neglected the final "else" for the last IIF, which is the not enough arguments error.

If you need to return numerics and text from a single formula, wrap the numerics in a totext().

-k

 

[Cheryl3D]

Hi synapsevampire:

Well okay maybe an IIF is not the best way to deal with this. Then how would you do the same with IF THEN statements in CR syntax. I'm very new to CR, especially using complex formulas. Could you provide some sample code, based on the Access snippet?

Access code:
=(IIf([RCost]>0,[RDescription],(IIf([NRCost]>0,[NRDescription],(IIf([NRManpower] Is Not Null,"Manpower Workyear Cost"))))))


Thanks for any help.
Cheryl

 

[bdreed35]

Try this:

If {qryAnnex_View.RCost} > 0 then
    (qryAnnex_View.RDescription}
Else If {qryAnnex_View.NRCost} > 0 then
    {qryAnnex_View.NRDescription}
Else If Not IsNull({qryAnnex_View.NRManpower}) then
    "Manpower Workyear Cost"
Else
    "Unknown" //replace with default if none of the above are true

~Brian

 

[Cheryl3D]

Hi bdreed35:

Thanks. It is close, however first you had a slight typo on line 2, I replaced the ( with a }beginning of line 2. But despite that correction, when I tried to validate your statement:

If {qryAnnex_View.RCost} > 0 then
{qryAnnex_View.RDescription}
Else If {qryAnnex_View.NRCost} > 0 then
{qryAnnex_View.NRDescription}
Else If Not IsNull({qryAnnex_View.NRManpower}) then
"Manpower Workyear Cost"
Else
"" //replace with default if none of the above are true

I received the following warning message:
The formula result must be a boolean.

What does it mean? And how can I resolve it?

Thanks again,
Cheryl

 

[synapsevampire]

Try:

If {qryAnnex_View.RCost} > 0 then
{qryAnnex_View.RDescription}
Else If {qryAnnex_View.NRCost} > 0 then
{qryAnnex_View.NRDescription}
Else If Not(IsNull({qryAnnex_View.NRManpower})) then
"Manpower Workyear Cost"
Else
""

In my previosu post I touched on using parentheticals with a NOT statement. You might also include a:

and {qryAnnex_View.NRManpower} <> 0 ...

if you want to disregard zero values (not the same as a null).

-k

 

[Cheryl3D]

Hi synapsevampire:

I tried the following and still received the same error message in CR:

If {qryAnnex_View.RCost} > 0 then
{qryAnnex_View.RDescription}
Else If {qryAnnex_View.NRCost} > 0 then
{qryAnnex_View.NRDescription}
Else If Not(IsNull({qryAnnex_View.NRManpower})) And ({qryAnnex_View.NRManpower} <> 0)then
"Manpower Workyear Cost"
Else
""

Cheryl

 

[Cheryl3D]

Hi Guys:

I got it to work. Your syntax was okay. But I read something that said I had to follow certain steps.
Create the formula
Display field list then click NEW.

I was doing the wrong way.

You all can still help me to convert the following IIF statement to an IF statement:

Original in Access 2000:
=(IIf([RCost]>0 And [NRCost]>0,[NRDescription],(IIf([RCost]>0 And [NRManpower] Is Not Null Or [NRCost]>0 And [NRManpower] Is Not Null And [SubCategory]<>"Logistics","Manpower Workyear Cost"))))


My attempt to convert to CR formula:
If ({qryAnnex_View.RCost} > 0 And {qryAnnex_View.NRCost} > 0) then
{qryAnnex_View.NRDescription}
Else If ({qryAnnex_View.RCost} > 0 And Not(IsNull( {qryAnnex_View.NRManpower})) Or ({qryAnnex_View.NRCost}> 0) And Not (IsNull({qryAnnex_View.NRManpower})) And ({qryAnnex_View.SubCategory} <> "Logistics"))Then
Else
"Manpower Workyear Cost"

What is wrong? I received an error message about the syntax.

Thanks,
Cheryl

 

[synapsevampire]

There's nothing after the last THEN:

If ({qryAnnex_View.RCost} > 0 And {qryAnnex_View.NRCost} > 0) then
{qryAnnex_View.NRDescription}
Else
(
If ({qryAnnex_View.RCost} > 0
And
Not(IsNull( {qryAnnex_View.NRManpower}))
)
Or
(
({qryAnnex_View.NRCost}> 0)
And
Not (IsNull({qryAnnex_View.NRManpower}))
)
And
({qryAnnex_View.SubCategory} <> "Logistics"))Then
<INSERT SOMETHING HERE>
Else
"Manpower Workyear Cost"

-k

 

[Cheryl3D]

Thanks sysnapsevampire:

I had to slightly adjust your SQL statement, but it worked great! See the following:

If ({qryAnnex_View.RCost} > 0 And {qryAnnex_View.NRCost} > 0) then
{qryAnnex_View.NRDescription}
Else
(
If ({qryAnnex_View.RCost} > 0
And
Not(IsNull({qryAnnex_View.NRManpower}))
)
Or
(
({qryAnnex_View.NRCost}> 0)
And
Not(IsNull({qryAnnex_View.NRManpower}))
)
And
({qryAnnex_View.SubCategory} <> "Logistics") then
"Manpower Workyear Cost"
Else
"Manpower Workyear Cost")

Here is one more to convert from Access 2000 to CR syntax. I'm beginning to get the gist of the CR If statement syntax.

Original in Access 2000:
=(IIf([RCost]>0 And [NRCost]>0 And [NRManpower] Is Not Null And [SubCategory]<>"Logistics","Manpower Workyear Cost"))

My attempt to convert to CR formula:
If {qryAnnex_View.RCost} > 0 And {qryAnnex_View.NRCost} > 0 And Not(IsNull({qryAnnex_View.NRManpower})) And {qryAnnex_View.Subcategory} <> "Logistics" Then
"Manpower Workyear Cost"

Please assist with conversion. Any help is greatly appreciated.

Thanks,
Cheryl