ratio between two calculated contros in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two sub reports and have calculated totals from them in the main
report. Now I want to show the ratio as 20:1 ( or 47:3, or whatever). Is
there a way to do it, or am I stuck with showing them as a percentage?
Either could be the greater

thanks in anticipation
 
Steph,
Try this, given 2 fields Num1 and Num2 and unbound field Ratio.
*This code is placed behind the AfterUpdate for BOTH Num1 and Num2

Private Sub Num1_AfterUpdate()
Dim sN1 As Single, sN2 As Single
If Num1 > Num2 Then
If Num1 Mod Num2 = 0 Then 'they are mutually divisible
[Ratio] = (Num1 / Num2) & ":" & Num2 / Num2
ElseIf Num2 Mod Num1 <> 0 Then 'they are NOT mutually divisible
[Ratio] = Num1 & ":" & Num2
End If
Elseif Num1 > Num2 Then
If Num2 Mod Num1 = 0 Then 'they are mutually divisible
[Ratio] = (Num1 / Num1) & ":" & Num2 / Num1
ElseIf Num2 Mod Num1 <> 0 Then 'they are NOT mutually divisible
[Ratio] = Num1 & ":" & Num2
End If
End If
End Sub

Any divisible ratio will be converted to lowest common denominator (10:2 =
5:1 or 2:10 = 1:5)
Any Non-divisible ratiuo will remain as is (9:2 =9:2 or 2:9 = 2:9))

hth
Al Camp
 
Steph,
Found a little syntax problem.
Try...
Private Sub Num1_AfterUpdate()
If Num1 > Num2 Then
If Num1 Mod Num2 = 0 Then 'they are mutually divisible
[Ratio] = (Num1 / Num2) & ":" & Num2 / Num2
ElseIf Num2 Mod Num1 <> 0 Then 'they are NOT mutually divisible
[Ratio] = Num1 & ":" & Num2
End If
ElseIf Num1 < Num2 Then ' ***** Had ">" here before... should
be "<"
If Num2 Mod Num1 = 0 Then 'they are mutually divisible
[Ratio] = (Num1 / Num1) & ":" & Num2 / Num1
ElseIf Num2 Mod Num1 <> 0 Then 'they are NOT mutually divisible
[Ratio] = Num1 & ":" & Num2
End If
End If
End Sub

hth
Al Camp
 
Back
Top