Using IIf in a report (1 Viewer)

[brianborn]

I have a report in Access 2010 that totals the amount of transactions that each department has spent and it appears in the summary footer. What I would like to show as well is the remaining amount of the department's budget and if there is not any funds left, to show a statement that there is no funds available in the budget.

Below is the code that I'm trying to use, but to the experienced developer, they should notice that it doesn't work. Will someone help me with the proper expression to use?

=DisplayText: IIf([Amount]>=700.00,"NO BUDGET REMAINING",
 IIf([Amount]<=0,[Amount]))

 

[plog]

On a report you don't use a name. This should be all you need:

=IIf([Amount]>=700,"NO BUDGET REMAINING", IIf([Amount]<=0,[Amount]))

Your logic means nothing will appear if [Amount] is >0 AND <700.

 

[brianborn]

Sometimes I do not have logic, but I'm trying to gain that knowledge. Now that I had a good night sleep, I took time to take a screenshot and hopefully better explain what I'm trying to do. Hopefully this will be of more help.

Inserting the image doesn't seem to work so here is the link.
http://img171.imageshack.us/img171/1904/58984815.png

Also I just noticed that the 'Amount' should be subtracted from 700 to get the remaining amount. Even with the code in the image, the 'Amount' is not correct as it should be equal to 'Total Spent' so changing the logic is not going to help as there must be something else wrong as well.

 

[brianborn]

I'm still struggling with the problem and hoping that someone will come to my rescue. Here is what I think is the closest to being correct, but I still get invalid syntax.

EDIT: the code below works.

=IIf([Sum of Amount]>=700,"NO BUDGET REMAINING",IIf([Sum Of Amount]<700,"Remaining Amount in the Budget: $" & (700-[Sum Of Amount])))

Is there anyone that can help me with this problem?

http://img171.imageshack.us/img171/1904/58984815.png

 

[brianborn]

If it helps, I used the following page for reference.

http://msdn.microsoft.com/en-us/library/gg241307.aspx

 

[plog]

The footer name can only be changed by using a different field to group your report or renaming that field. Do you want to group your report by a field other than [Coach's Name]?

Try this for the budget message area:

=IIf(Sum([Amount])>700, "No Budget Remaining", "Budget Remaining: $" & 700-Sum([Amount]))