Values from a table to a form 1

[cjmcfg]

I'm trying to call a value from another table into a field on a form. Below is my code...I'm getting a "Argument not optional" error

Me![BM%] = IIf(Table.[Employee Data].[Office] = Me![Office], IIf(Table.[Employee Data].[Title] = "Branch Manager", Table.[Employee Data].[Commission %], 0))

 

[Remou]

You need three arguments per IIf, you only have 2 for the outer IIf.

 

[cjmcfg]

right but doesn't the last 0 count as the false for both IIF statements? if not where do i put the other 0

 

[Remou]

Me![BM%] =
[tt]IIf(Table.[Employee Data].[Office] = Me![Office],
IIf 1=True: IIf(Table.[Employee Data].[Title] = "Branch Manager",
IIf 2=True: Table.[Employee Data].[Commission %],
IIf 2=False: 0)
Iff 1=False: ???)[/tt]

 

[cjmcfg]

VB says the 1's and 2's are invalid characters

 

[Remou]

What I was showing is that you have not entered an argument for when the first IIF is false:

[tt]Iff 1=False: ???)[/tt]

An argument needs to be added before the final bracket.

 

[cjmcfg]

I put in

Me![BM%] = IIf(Tables![Employee Data]![Office] = Me![Office], IIf(Tables![Employee Data]![Title] = "Branch Manager", Tables![Employee Data]![Commission %], 0), 0)

and it gives me a runtime error and says Object Required

 

[Remou]

I should have noticed that you are referring to a Table object. This will not work, you need a Form or such like:

Forms![Employee Data]![Office]

 

[cjmcfg]

but...i need too. i need to search employee records for branch managers of a specific office and pull forward their commission %'s how else would i do this?

 

[Remou]

Ok. You need a DlookUp in there somewhere.

[tt]IIf(DlookUp("Office","[Employee Data]","[Office] ='" & [Office] & "'"), IIf(DlookUp("Title","[Employee Data]","[Title] ='Branch Manager'"), DlookUp("[Commission %]","[Employee Data]"), 0), 0)[/tt]

But I am guessing. There probably should be a reference to the Employee number in there somewhere.

I imagine you want something closer to:

[tt]Nz(DlookUp("[Commission %]","[Employee Data]","[Office] ='" & [Office] & "' And [Title] ='Branch Manager' And EmployeeID=" & EmployeeID),0)[/tt]