Connie,
Wasn't it you or someone else that John recently pointed out the use of "" rather than 0 or Null in an expression?
An IIf() should only return a single data type or null. Replace the "" with 0. "" is not a number that you can Avg() or format as a number.
Try:
Ratio: IIf([qCurrentPriceAll].[CurrentPrice]=0 Or [qCurrentPriceAll].[CurrentPrice] Is Null,0,[Sales]![SoldPrice]/[qCurrentPriceAll].[CurrentPrice])
OR:
Ratio: Val(IIf([qCurrentPriceAll].[CurrentPrice]=0 Or [qCurrentPriceAll].[CurrentPrice] Is Null,0,[Sales]![SoldPrice]/[qCurrentPriceAll].[CurrentPrice]))
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> Good morning. I have a query that computes a ratio of Sold Price to Last Listed Price using the following:
>
> Ratio: IIf([qCurrentPriceAll].[CurrentPrice]=0 Or [qCurrentPriceAll].[CurrentPrice] Is Null,"",[Sales]![SoldPrice]/[qCurrentPriceAll].[CurrentPrice])
>
> On the report I would like it to display as a percentage. On the Property Sheet the Format for the field is Percent. (Percent is the only option given). I select 2 decimal points. An example of the result that shows is: 0.923248053392658 whether it's auto decimal places or 2 decimals. I want it to display 92.32%. What should I do?
>
> Also, at the top of the report I tried to create a box that displayed the average ratio. The Control Source was set to = Avg([Ratio]). The message said that "the expression is typed incorrectly or is too complex to be evaluated ..."
>
> Thank You!
> Connie
>
Tidak ada komentar:
Posting Komentar