IIF condition in Access 2010

[mba_110]

Hello everyone,

I am doing a small project in access and came up with below problem.


=IIf([Emp_IDCombo]=[EOSB_Query]![Emp_ID],[EOSB_Query]![GrossSalary]-[Contracts_table]![GrossSalary])

I have text box on form and i want to calculate two fields one from table and another from query and return the value based on combo box i have on form i try to manage the above but something is missing.

By reading above you will understand what i am trying to do.

Please reply if you are expert in IIF

thanks & Regards,

MBA

 

[Tubal]

Hello MBA

I see a couple of issues to check:
1) Ensure [Emp_IDCombo] returns the [Emp_ID] - that is to say, the Emp_IDCombo bound column should match [Emp_ID] (name vs ID number)
2) Make sure the forms referenced (EOSB_Quary and Contract_table) are open. (If they are not forms then you will need some inner joins somewhere in your query and apply the correct syntax.)
3) The IIF(logic test, TRUE, FALSE) format is missing the FALSE portion, which is required.
4) The data from the table and the query are accessible via DLookup (assume data is not in the current form data source)

Tubal

Don't forget to use the VBA Immediate Window ( Ctl+g ) to quickly test out syntax.

 

[mba_110]

Thanks Tubal,

1. I am doing this exercise on form called EOSB_Form.
2. Emp_IDcombo is combo box on above form and has row source SELECT [Employees_table].[Emp_ID], [Employees_table].[EmployeeName] FROM Employees_table;
3. EOSB_Query is a Access Query that have created with multiple tables data.
4. Contract_table is a table with list of data with employee IDs.
5. The false portion should be zero if not true.

Now tell me what joints you need me to fix?

 

[Tubal]

MBA,

I will have to provide some general guidance.

Use DLookup to get a value from a table or query.
Be sure to provide all three arguments for IIF.
For the ComboBox look at what the "bound" column is and use the proper index when you compare it.
I don't think there is an issue with inner-joins. - that is hard to tell without seeing the details.

build each part of the IIF statement in different fields to test if / how then work. Once they are working, put it in the IIF.

Tubal