if...then conditions ???

[em07189]

Hi everyone!

it's possible to do a lookup list with a if condition in access.

for example:

if the value of a field is "a" then link to the table "x"
if the value of a field is "b" then link to the table "y"

thanks.

[maximus]

Sure e.g.

I have two tables:

Table1={p_id,F_Name,age,Sex,Designation} Senior managers
Table2={p_id,F_Name,age,Sex,Designation} Junior Managers


Suppose I have a form (Data_entry) with p_id, Name, Designation

now I have to lookup the age of the person. Now if the designation is senior manager the I want to lookup table1 and when junior managers Table2. In this example we will lookup age from the tables based on an if condition on designation.

Dim intAge as integer

Select Case Me.Designation
Case is ="Senior Manager"
intAge=Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id)
Case is = "Junior Manager"
intAge=Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)
End Select

This is using Select case. Now with if

Dim intAge as integer
if Me.Designation="Senior Manager" then
intAge=Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id)
elseif Me.Designation="Junior Manager" Then
intAge=Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)
end if

Now this is another way of doing it:
Dim intAge as integer
intAge=iif(Me.Designation="Senior Manager",Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id),iif(Me.Designation="Junior Manager",Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)))

These are the following ways how you can use if with lookup.

if this solves your problem please mark the thread to be solved.

[em07189]

Hi maximus!

were do you enter this code? is this visual basic?

I'm a novice in Access.

Sure e.g.

I have two tables:

Table1={p_id,F_Name,age,Sex,Designation} Senior managers
Table2={p_id,F_Name,age,Sex,Designation} Junior Managers


Suppose I have a form (Data_entry) with p_id, Name, Designation

now I have to lookup the age of the person. Now if the designation is senior manager the I want to lookup table1 and when junior managers Table2. In this example we will lookup age from the tables based on an if condition on designation.

Dim intAge as integer

Select Case Me.Designation
Case is ="Senior Manager"
intAge=Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id)
Case is = "Junior Manager"
intAge=Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)
End Select

This is using Select case. Now with if

Dim intAge as integer
if Me.Designation="Senior Manager" then
intAge=Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id)
elseif Me.Designation="Junior Manager" Then
intAge=Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)
end if

Now this is another way of doing it:
Dim intAge as integer
intAge=iif(Me.Designation="Senior Manager",Dlookup("[age]","Table1","p_id"="& Forms!Data_entry!p_id),iif(Me.Designation="Junior Manager",Dlookup("[age]","Table2","p_id"="& Forms!Data_entry!p_id)))

These are the following ways how you can use if with lookup.

if this solves your problem please mark the thread to be solved.

[maximus]

My question is where do you want to use it tell me and I will tell you how.

These codes can be used in the afterupdate events of controls like text boxes and comboboxes and even on onclick events of text boxes.

[em07189]

Hi Manimus!

I want to use it in a field of a table that is a like listbox .

but this code is done in vbA with a form?

My question is where do you want to use it tell me and I will tell you how.

These codes can be used in the afterupdate events of controls like text boxes and comboboxes and even on onclick events of text boxes.

[maximus]

Now your requirement is clear

Suppose I have Three tables

Table1={p_id,F_Name,age,Sex,Designation}
Senior_manager_salary={p_id,Salary}
Junior_manager_salary={p_id,Salary}


if designation is senior manager you will lookup up Senior_manager_salary or Junior_manager_salary.
Create a query in the design view from table Table1.
In a blank colunm type:

Salary:iif([Designation]="Senior Manager",Dlookup("[Salary]","Senior_manager_salary","[Senior_manager_salary].[p_id]="&[Table1].[p_id]),iif([Designation]="Junior Manager",Dlookup("[Salary]","junior_manager_salary","[Junior_manager_salary].[p_id]="&[Table1].[p_id])))

if this solves your problem please mark this thread solved.

[maximus]

were you able to solve your problem.