#Num!

[Hillbillie]

Here is my problem. I have this report that I get a sum.

I use this in my a text box called SumBox

I put this in the Control Source =Sum(IIf([grade]="A",4*[hourscred],IIf([grade]="B",3*[hourscred],IIf([grade]="C",2*[hourscred],IIf([grade]="D",1*[hourscred],0)))))/Sum(IIf([grade] In ("A","B","C","D"),[hourscred],0))


This works well except when it is trying to divide by zero on the report and it gives #Num! on the report. I would like for it to read 0

I didnt see anything exactly like this in the forum and would appreciate someone letting me know what I need to change.
Thanks

 

[Lilliabeth]

Try this...

=IIF(
[grade] Not In ("A","B","C","D"),0,
IIf([grade]="A",4*[hourscred],
IIf([grade]="B",3*[hourscred],
IIf([grade]="C",2*[hourscred],[hourscred]
))))
/[hourscred]

 

[Hillbillie]

Thanks Lilliabeth for the quick reply.

That worked in getting a 0 instead of #Num!, but it changed the calculation in my original Control Source formula.

Thanks

 

[Lilliabeth]

Maybe you could be a bit more specific? What is different?

 

[Hillbillie]

Yes, that was a big shy on details.

The original Control Source formula would sum up all grades A thru D and give a final average of the courses taken and then divides the sum of more stuff.

The latest formula did not give anything but a zero value. I didnt see any Sum.

Keep in mind that I do not know much programming, you are the expert.

 

[Lilliabeth]

Try this:

=IIF(
[grade] Not In ("A","B","C","D"),0,

Sum(
IIf([grade]="A",4*[hourscred],
IIf([grade]="B",3*[hourscred],
IIf([grade]="C",2*[hourscred],[hourscred]
)))))
/[hourscred]

 

[Lilliabeth]

OMG, I need more coffee

=IIf([grade] Not In ("A","B","C","D"),0,Sum(IIf([grade]="A",4*[hourscred],IIf([grade]="B",3*[hourscred],IIf([grade]="C",2*[hourscred],[hourscred])))))/Sum([hourscred])

 

[dhookom]

I would probably create a simple user-defined function to convert the grade letter to a numeric value or use a small lookup table.
Another expression that should work is:

=Sum(IIf(InStr("DCBA",[Grade])=0,0,InStr("DCBA",[Grade])*[HoursCred]))/Sum([HoursCred])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Hillbillie]

Hey Lilliabeth, that coffee must have worked.

That code worked perfect. Thanks,