how to display when percent greater than 100%

[barlee]

I have several percent fields that when added up equal more than 100%. I want the report to display '100%' even if the total comes to 101%. I am pretty sure I need an IIf statement, but I can't for the life of me figure out how to write it.

here is what I have:

=IIf([grand totals.percent of current support owed]>100,100)

but I get '#Error ' instead of '100%'

when I put
=IIf([grand totals.percent of current support owed]>100,100,[grand totals.percent of current support owed])

I get #Name error

 

[georgedwilkinson]

=IIf([grand totals.percent of current support owed]>100,100,[grand totals.percent of current support owed])

Try:

=IIf([grand totals.percent of current support owed]>100,100,[grand totals].[percent of current support owed])

You need to put brackets around each object name (not around 2 different object names at once). You can avoid this type of problem by using object names without special characters and spaces.

HTH.

 

[HiTechCoach]

I have several percent fields that when added up equal more than 100%. I want the report to display '100%' even if the total comes to 101%. I am pretty sure I need an IIf statement, but I can't for the life of me figure out how to write it.

here is what I have:

=IIf([grand totals.percent of current support owed]>100,100)

but I get '#Error ' instead of '100%'

when I put
=IIf([grand totals].[percent of current support owed]>100,100,[grand totals].[percent of current support owed])

I get #Name error

What is the name of the text box control? make sure that it does not match the name of the field in the IIF() statement.


100% = 1

So I think you probably need to use:

=IIf([grand totals].[percent of current support owed] > 1, 1,[grand totals].[percent of current support owed])

 

[gemma-the-husky]

out of interest, how do you get the % greater than 100%

if you are adding rounded values, instead add the unrounded values, and then you shouldnt get the error

 

[barlee]

To gemma-the-husky: I didn't round any of the values being added. I did limit the decimals to 4 and when I sum the values they end up being GT 100%.

to HighTechCoach: I tried the following statement

=IIf([grand totals].[percent of current support owed] > 1, 1,[grand totals].[percent of current support owed])

and still got the #Name error

to georgedwilkinson: I went back and looked and sure enough the name of the text box control was the same as the name of the field in the IIf statement. I have made several changes. I re-ran my query so that the field names do not have any spaces (replaced with underscore) and changed the name of the text box control.

so, now I have this statement:

=IIf(grand_totals.percent_of_current_support_owed>1,1,grand_totals.percent_of_current_support_owed)

and it still is giving me the #Name error.

the name of the text box control is Grand_Total_Percent_of_Current_Support_Owed

the table is named Grand_Totals and the column is named Percent_of_Current_Support_Owed.

I am obviously still missing something in the naming convention, since the error is #Name, right?

 

[HiTechCoach]

To gemma-the-husky: I didn't round any of the values being added. I did limit the decimals to 4 and when I sum the values they end up being GT 100%.

to HighTechCoach: I tried the following statement

=IIf([grand totals].[percent of current support owed] > 1, 1,[grand totals].[percent of current support owed])

and still got the #Name error

to georgedwilkinson: I went back and looked and sure enough the name of the text box control was the same as the name of the field in the IIf statement. I have made several changes. I re-ran my query so that the field names do not have any spaces (replaced with underscore) and changed the name of the text box control.

so, now I have this statement:

=IIf(grand_totals.percent_of_current_support_owed>1,1,grand_totals.percent_of_current_support_owed)

and it still is giving me the #Name error.

the name of the text box control is Grand_Total_Percent_of_Current_Support_Owed

the table is named Grand_Totals and the column is named Percent_of_Current_Support_Owed.

I am obviously still missing something in the naming convention, since the error is #Name, right?

Did you also see this is my previous post:

What is the name of the text box control? make sure that it does not match the name of the field in the IIF() statement.

This can cause the #Name issue.

 

[Brianwarnock]

I must be a bit dense as I don't get this at all.

You have a table named Grand_Totals and a column named Percent_of_Current_Support_Owed.


You have a query in which you Sum this column and a Report which is presumable based on this query so in the Text Box on the Report I think it should be

IIF(SumOfPercent_of_Current_Support_Owed>1,1,SumOfPercent_of_Current_Support_Owed)

Brian

 

[barlee]

well, here's the thing. this report is not based on one query, it is based on 4 queries. I have a table of people that owe money, and another of people that paid (don't ask why two tables, it is just the way the data is given to me). I have run a query to make a table of people that paid in full, another query/table of people that paid partial, and yet another query/table of people that paid nothing. I had made another table of the grand totals, but I see now that probably wasn't necessary.

I need to show all three categories on one report and the totals, and since they were all separate tables, I couldn't figure out how to display the results (# of cases, percent of cases that paid, # of current support owed that was paid, grand totals of all three) so I just created unbound text boxes and associated each box with the desired data to be displayed from the individual queries.

I have attached a copy of the report as it looks right now (rough) so you can see what I am trying to do. The problem arises that the Grand Total of the % of current support owed adds up to more than 100%, so I just want to display 100% even if it is slightly more...

 

[barlee]

ok, I went back and changed the name of the column in my Grand_Totals table to be Sumof_Percent_of_Current_Support_Owed

and used the following IIf statement:

=IIf([Sumof_Percent_of_Current_Support_Owed]>1,1,[Sumof_Percent_of_Current_Support_Owed])

and sonuvagun, it worked! WOO-HOO!

Thanks for the input everyone, it really helped push me in the right direction!

 

[andrewneal]

I need to display exact numbers greater than 100% for my manning database. For instance if there are only 10 authorized and we have 12 currently employed then that position is 120% filled.