IIF condition in Access 2010 (1 Viewer)

[mba_110]

I have been asking this question in another forum but unfortunately didn't get proper reply.

If anyone can help here would be great.

I have form called EndofSerivce_Form on this form i have combobox called Emp_IDCombo i am pulling many employee records from different tables and queries using Dlookup but i struct with one text box on this form which is [housingtxt] in this text box i need to pull information from table and use it based on IIF condition.

Let me tell you clearly.

Emp_IDcombo is employee ID once i select any Employee ID the remaining fields of my text boxes on form is changing the information automatically based on that selection it pull the information from Employees_table but i am trying to take information of that employee housing from Contract_table's [Housing] because employees_table is having personal records of employee and not contractual records, so if that employee doesnt have housing than it should [basic] from contracts_table x 2 /12 if in case that employee is having housing amount in contracts_table [Housing] then it should show the same value.

row source of my form is SELECT [Employees_table].[Emp_ID], [Employees_table].[EmployeeName] FROM Employees_table;


Please help with expression,code anything that can help me to solve.

 

[vbaInet]

I don't completely follow but I'm wary about your use of DLookups().

* Can you upload a screenshot of your form in Design View.
* What is the Default View of your form?

 

[jdraw]

I'm not following your post either. Your description is a combination of English, Access terms and some logic indicating HOW you have done something. I suggest you step back and tell us in plain, simple English what you are trying to do --no Access jargon -- until we understand your situation. A description of your current tables, and the intended purpose of the form involved would also be helpful.

 

[mba_110]

Here you go default view is Single form.

 

[jdraw]

Now I'm also concerned with your use of DLOokups.

A jpg of your form design does not help me with

I suggest you step back and tell us in plain, simple English what you are trying to do

nor

A description of your current tables, and the intended purpose of the form involved would also be helpful.

Readers should not have to guess WHAT you are trying to do; WHAT tables and fields you have; WHAT relationships exist between your tables.

 

[mba_110]

I have two tables involved in this expression the names are Employees_table & Contract_table.

I have form name Endofservice_form and it has Combo box named Emp_IDcombo and many other text boxes as per form requirement & Purpose I am calling data from these two tables with Dlookup function based on Combo box selection.

Contracts table contain contractual data & benefits of employees and employees table contain personal information for employees these two tables data I am pulling on this form with Dlookup function based on combo box selection.

Relationships are: Employees table [Emp_ID] (PK) to Contracts table (FK) on to many relationships.


Now I need to get information from contracts table on this form based on combo selection which is employee ID on this form once I select any employee ID from combo box I need this text box should evaluate the Contracts_table [Housing] if its zero than it should calculate [basic] field on contracts_table and multiply by x 2 and divide by / 12 if contracts_table [housing] is having any number other than zero than it should represent that number as it is in text box of form.

Field [basic] in contracts table is a numeric field.

Can you do it?

 

[vbaInet]

My suspicions of your use of the DLookup() function was correct.

First of all, you don't use DLookups in that way. Your form should be bound (or attached) to a table or query. Are you new to Access?

I would advise that you use the Form Wizard to build your form. Please look into that.