Please help with formula

[lbodie]

I am reading three tables and trying to get the best vendor number from all three. I keep getting wrong number of arguments. Can someone help? Thanks!

vendr: (IIf(IsNull([salesfil]![VENDOR_NUMBER]) And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER]) And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]))),"UNKNOWN",
(IIf(IsNull([salesfil]![VENDOR_NUMBER]) And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER]),[OAUSER_NSVEND1]![VEND_NUMBER]),IIf(IsNull([salesfil]![VENDOR_NUMBER]) And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),[OAUSER_ITEMLU1]![VENDOR_NUMBER]),IIf(IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER]) And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),[salesfil]![VENDOR_NUMBER]))

 

[JoeMiller]

Your very last IIF statement is missing it's FALSE part.

It may help to look at it like this:

[tt]
vendr: IIf(IsNull([salesfil]![VENDOR_NUMBER])
And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER])
And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]))),

"UNKNOWN",

IIf(IsNull([salesfil]![VENDOR_NUMBER])
And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER]),
[OAUSER_NSVEND1]![VEND_NUMBER]),

IIf(IsNull([salesfil]![VENDOR_NUMBER])
And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),
[OAUSER_ITEMLU1]![VENDOR_NUMBER],

IIf(IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER])
And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),
[salesfil]![VENDOR_NUMBER]))[/tt]

Joe Miller
joe.miller@flotech.net

 

[lbodie]

Ok, I added a false part to it and am still getting errors! Please help me figure this out!

 

[MichaelRed]

Public Function basSelVend(Optional Vend1 As Variant, _
Optional Vend2 As Variant, _
Optional Vend3 As Variant) As Variant

Dim MyVend(3) As Variant
'vendr:

'[salesfil]![VENDOR_NUMBER] 'Vend1
'[OAUSER_ITEMLU1]![VENDOR_NUMBER] 'Vend2
'[OAUSER_NSVEND1]![VEND_NUMBER] 'Vend3

MyVend(1) = Nz(Vend1)
MyVend(2) = Nz(Vend2)
MyVend(3) = Nz(Vend3)

'IIf(IsNull([salesfil]![VENDOR_NUMBER])
'And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER])
'And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]))),

'"UNKNOWN",

If (MyVend(1) = 0 And MyVend(2) = 0 And MyVend(3) = 0) Then
basSelVend = "UnKnown"
Exit Function
End If


'IIf(IsNull([salesfil]![VENDOR_NUMBER])
'And IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER]),
'[OAUSER_NSVEND1]![VEND_NUMBER]),

If (MyVend(1) = 0 And MyVend(2) = 0) Then
basSelVend = Vend3
Exit Function
End If

'IIf(IsNull([salesfil]![VENDOR_NUMBER])
'And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),
'[OAUSER_ITEMLU1]![VENDOR_NUMBER],

If (MyVend(1) = 0 And MyVend(3) = 0) Then
basSelVend = Vend2
Exit Function
End If


'IIf(IsNull([OAUSER_ITEMLU1]![VENDOR_NUMBER])
'And IsNull([OAUSER_NSVEND1]![VEND_NUMBER]),
'[salesfil]![VENDOR_NUMBER]))

If (MyVend(2) = 0 And MyVend(3) = 0) Then
basSelVend = Vend1
Exit Function
End If

End Function
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over