IF Statement error

[alec1982]

Hi anybody can help me figuring out what's wrong in this IF statement.

if (ExpenseFrequency ='Monthly' & (Year([Payment Date]) = Year(ExpenseStartDate)) &(month(ExpenseStartDate) = month([Payment Date]))& (Day(ExpenseStartDate)<>Day([Payment Date])),

                            (([Expense Amount in USD]/Day(MonthEnd([Payment Date])))*(Day(MonthEnd([Payment Date]))-Day(ExpenseStartDate)+1)) ,[Expense Amount in USD])  as [Expense Amount (USD)] ,

Like on one of the records I have:

ExpenseFrequency= Monthly

PaymentDate 2008-10-01

ExpenseStartDate=2008-10-15

[Expense Amount in USD]= 4504.17 (this amount after the calculations in the if satement above should be 2470.03 ).

Thanks,

[#2 · Accepted solution]

replace  & with and. & is used to concate strings

[#3]

if(

ExpenseFrequency ='Monthly' &

should be

if(

ExpenseFrequency ='Monthly' and

Also for other cases...

Use & for concattenating 2 fields (like load fieldA&'someText'&fieldB)

Use and for combining conditions (like if(1=1 and 2=2 and 3=3,'valueIfTrue','valueIfFalse')

Hope it helps!

[alec1982]

This working perfectly,

do you guys have anyway of simplifying the statement above.

Like if you notice i am checking if Year(X) = Year(Z) and month(X)=Month(Z) and Day(X)<>Day(Z)

let me know and thanks for your help.

[#5]

You can replace the year and month with monthname() This is a combination of the month and year

Monthname(X) = Monthname(Z) and Day(X)<>Day(Z)