Calculations in queries 2

[tarena]

I have a query with calculations that divide one field by another however, if one of the fields is a zero the result comes back as #error. The field needs to have a value or the total will not calculate. How can I get the #Error to show as either a blank or a zero?

Thank you
Tarena

 

[scorpio1769]

Tarena,

Use a logical if, then, else. For example, say you wanted to calculate sales percentage with the two fields "calls" and "Sales". The math is sales/calls, however if calls are = 0 you get an error as you already know. So, do this in your query:

Sales Percentage: iif([calls]=0,"No calls",[sales]/[calls])

Good luck

 

[tarena]

Absolutely perfect...thank you so very much, knew I could count on Tek-Tips

 

[tarena]

...now how can I get that to come through as a percentage???

 

[scorpio1769]

Either set the format of the field to Percent (I think it's an option) or just add some logic to the expression

iif([calls]=0,"No calls",round(([sales]/[calls])*100),2)

This multiplies the answer times 100 and then rounds to 2 decimal places.

 

[tarena]

When I do that I get an error saying...The expression you entered has a function containing the wrong number of arguments. And there isn't a property option for percentage once I enter the expression.

 

[scorpio1769]

try

iif([calls]=0,"No calls",(round(([sales]/[calls])*100),2))

 

[tarena]

That doesn't work either. Now I get..."The expression you entered contains invalid syntx, or you need to enclose your text data in quotes. My actual expression is...txtFirst: IIf([FirstHours]=0,"0",(round(([FirstTotalParts]/[FirstHours])/[RatePerHr])*100),2))

 

[tarena]

Sorry, that was one I tried last, my actual expression is actually...txtFirst: IIf([FirstHours]=0,"0",(round(([FirstTotalParts]/[FirstHours]/[RatePerHr])*100),2))

 

[tarena]

Thank you scorpio1769, I figured the rest of it out with your help. I just did the calculation in the report and it works just fine.

Thank you so much.

 

[scorpio1769]

glad to hear it.

 

[lespaul]

tarena,

Thanks are "shown" by awarding "stars"....just click on the 'Thank scorpio1769 for this valuable post' link in a post from scorpio1769.

Leslie