Calculated field on form - VBA 1

[dacllog]

Hi friendly helper,

I am lost!

I have 1 'Result field' on a form called 'FC8'.

This formular works in Excel, but not in Access (too many IIF's I Guess?).
=IF(SUM(M6:M8)=0;0;IF(SUM(M7:M8)=0;H6;IF(SUM(M6:M7)=0;H8;IF(SUM(M6;M8)=0;H7;IF(M6=0;MAKS(H7:H8);IF(M7=0;MAKS(H6;H8);IF(M8=0;MAKS(H6:H7);MAKS(H6:H8))))))))

How would I write this in VBA Access, so that my 'Result Field' (FC8) equals the above 'Excel if' formular?

Thanks!

Daniel

(can't get back to you before monday).

 

[MajP]

I am not sure how you are planning to use this. Are you working with an Excel Object from Access, or have you built a bunch of controls to mimic a spread sheet range?

The reasons that this does not work are many. The "IF" function is an Excel function where the "IIF" is an Access function. I do not know why there is a difference.

However, nested "iiF" are very slow and difficult to write. I would build my own function to do this using if then else construct. In this example my assumption is that m6,m7,m8,h6,h7,h8 are now fields on a form. Also I do not know what the MAKS function is so I just but some dummy result.

Public Function setValue(M6 As Double, M7 As Double, M8 As Double, H6 As Double, H7 As Double, H8 As Double) As Double
  
  If M6 + M7 = 0 Then
  ElseIf M7 + M8 = 0 Then
     setValue = H6
  ElseIf M6 + M7 = 0 Then
     setValue = H8
  ElseIf M6 + M8 = 0 Then
     setValue = H7
  ElseIf M6 = 0 Then
     setValue = H7 + H8
  ElseIf M7 = 0 Then
     setValue = H6 + H8
  ElseIf M8 = 0 Then
     setValue = H6 + H7
  Else
     setValue = H6 + H8
  End If
End Function

demo
?setValue(0,0,3,4,5,6)
0
?setValue(1,0,-1,4,5,6)
5
?setValue(1,2,0,4,5,6)
9

Although this takes more lines it is far,far more efficient than nested iifs.

also you can pass values from a control like this in the forms module.

x = setValue(Me.txtBxM6.value,me.TxtBxM7.value,...)

or in a forms calculted control

=setValue([txtBxM6],[txtBxM7]...)