Report - code builder

[Ciprian]

Hi to all,

what is the code equivalent for

if ([FieldA] < [StartDate]) then
[FieldB].value=0
end if

[StarDate] is a Date parameter which i entered when the report is created
[FieldA] is a Date field
[FieldB] is a Number field

i want to insert this in the code builder to run before the report is created

 

[Ciprian]

Here's a .jpg to help explain my problem better

the Report is supposed to show the Total Income for the period entered. My problem is with the Addendum (Act aditional in the report), since it's linked to the Contract and i need to show the contract info for that addendum, but i also have to show contracts info.

The problems appears with addendum for 2012 which have a contract done in 2011, because the info from 2011 appears in the report and affect the sums.

That is why i was trying to resolve this by code.

 

[vbaInet]

Here's how to get the correct Sum:

=SUM(IIF([[COLOR=Red]DateField[/COLOR]] >= [[COLOR=Red]StartDate[/COLOR]] And [[COLOR=Red]DateField[/COLOR]] <= [[COLOR=Red]EndDate[/COLOR]]), [[COLOR=Red]AmountField[/COLOR]], Null)

Amend the field names in red.

To be honest, I think your users will be confused when they seea Sum that doesn't add up.

 

[Ciprian]

Here's how to get the correct Sum:

=SUM(IIF([[COLOR=Red]DateField[/COLOR]] >= [[COLOR=Red]StartDate[/COLOR]] And [[COLOR=Red]DateField[/COLOR]] <= [[COLOR=Red]EndDate[/COLOR]]), [[COLOR=Red]AmountField[/COLOR]], Null)

Amend the field names in red.

To be honest, I think your users will be confused when they seea Sum that doesn't add up.

well ya, that's why i want to change the report, so that it will show the correct sum

Edit: i get an error with the formula: "The expression you entered has a function containing the wrong number of arguments"

=SUM(IIF([Data_Finalizare_Contract] >= [StartDate] And [Data_Finalizare_Contract] <= [EndDate]), [Contracte_Valoare_Contract_Lei], Null)

 

[vbaInet]

Why are those records outside the date range being displayed?

 

[Ciprian]

Why are those records outside the date range being displayed?

because of the link between the addendum and the contract (contract was made in 2011 and addendum in 2012) and since i want both contract and addendum in the same report i get that problem

 

[vbaInet]

So do you want to blank out the Dates and Amounts that are not in the range?

 

[Ciprian]

So do you want to blank out the Dates and Amounts that are not in the range?

wiht Conditional Formatting i know how to do it, but it still affects thee SUM. I need a way to change the amount to 0 where this is necessary and after that blank out

 

[vbaInet]

wiht Conditional Formatting i know how to do it, but it still affects thee SUM. I need a way to change the amount to 0 where this is necessary and after that blank out

Use the code I gave you to get the total Sum(), by the way it's missing an extra closing brace at the very end.

Then use the IIF() part of the code to set the control to 0 or "N/A". Replace Null with 0.

 

[Ciprian]

Use the code I gave you to get the total Sum(), by the way it's missing an extra closing brace at the very end.

Then use the IIF() part of the code to set the control to 0 or "N/A". Replace Null with 0.

even with the extra closing brace, the Sum() still gives an error
as far as the IIF() not really sure where i'm supposed to put that

 

[vbaInet]

What is the error message?
Let me see what you wrote as well.

 

[Ciprian]

What is the error message?
Let me see what you wrote as well.

error message : "The expression you entered has a function containing the wrong number of arguments"

this is what i wrote

=SUM(IIF([Data_Finalizare_Contract] >= [StartDate] And  [Data_Finalizare_Contract] <= [EndDate]),  
[Contracte_Valoare_Contract_Lei], Null))

 

[vbaInet]

Oops...

=SUM(IIF([Data_Finalizare_Contract] >= [StartDate] And  [Data_Finalizare_Contract] <= [EndDate],  
[Contracte_Valoare_Contract_Lei], Null))

 

[Ciprian]

Morning,

unfortunately it still does not let me enter this in the unbound text box or modify the normal =sum() one that i have now.

 

[Ciprian]

how about a subquery ? can't it help me ?

this is my query now

SELECT A,B,C,D,E,F from Contracts LEFT JOIN Addendum 
WHERE (( (F) between [StardDate] and [EndDate])));

can't i add another Select and it will be something like this ?

SELECT A,B,C [B](SELECT C from Contracts where 
(((C) between [StardDate] and [EndDate]))))[/B],D,E,F from Contracts LEFT JOIN Addendum 
WHERE (( (F) between [StardDate] and [EndDate])));

or something like this

SELECT A,B,C [B](IIF(C<[StartDate],0,1))[/B],D,E,F from Contracts LEFT JOIN Addendum 
WHERE (( (F) between [StardDate] and [EndDate])));

So what do you think is this possible ? If yes, please tell me how the right syntax is. thanks

 

[Ciprian]

Finally manged to do it

=Sum(IIf([Data_Finalizare_Contract]>=[StartDate] And 
[Data_Finalizare_Contract]<=[EndDate][B];[/B][Contracte_Valoare_Contract_Lei][B];[/B]0))

the expression was wrong because i used , and not ;

this and conditional formatting should do the trick

thanks for the help

 

[vbaInet]

Yes, some countries use ";" as the delimeter. Over here it's ","

Happy to help!