Nested IIF Dlookup Possible?

[TomH71]

I'm trying to create a nested IIf Dlookup without success.

Here is my form.



I am trying to look up a wage rate for a particular Job Number based on a drop down list on a form but... the formula needs to reference the field in a table that matches the selected drop down.

So if I select “SiteLead” from the drop down in the form for Worker1, (on form), I want it to return the value of 8.68 from the table below.



This I have been able to do no problem. I used an After Update event procedure to create a Dlookup. Here is the code that works, but obviously only if “SiteLead” is selected.



What I can’t do is refer to the other columns if the selection changes from "SiteLead" to "FloorTech1".

I tried to create a nested IIF function but without success. Can anyone give me some advice to this newbie?

 

[Solo712]

I'm trying to create a nested IIf Dlookup without success.

Here is my form.

View attachment 51081

I am trying to look up a wage rate for a particular Job Number based on a drop down list on a form but... the formula needs to reference the field in a table that matches the selected drop down.

So if I select “SiteLead” from the drop down in the form for Worker1, (on form), I want it to return the value of 8.68 from the table below.

View attachment 51082

This I have been able to do no problem. I used an After Update event procedure to create a Dlookup. Here is the code that works, but obviously only if “SiteLead” is selected.

View attachment 51083

What I can’t do is refer to the other columns if the selection changes from "SiteLead" to "FloorTech1".

I tried to create a nested IIF function but without success. Can anyone give me some advice to this newbie?

Make the first argument in the DLookup function point to the value of the combobox; ie.

Worker1Rate = Nz(DLookup(Worker1, "WageRates","JobNumber=" & JobNumber))

The code will substiture a value of the dropdown list selected for Worker1, ie "SiteLead", "FloorTech1", etc.

Best,
J.

 

[TomH71]

First of all thank you for your help. Using you advice, I managed to get the first selection to work with this code. It returned the value of 8.68.

Worker1Rate = IIf(Nz(Worker1 = 1), DLookup("SiteLead", "WageRates", "JobNumber=" & [JobNumber]), "")

HOWEVER...

Nesting the IIF caused me problems. The following did not work

Worker1Rate = IIf(Nz(Worker1 = 1), DLookup("SiteLead", "WageRates", "JobNumber=" & [JobNumber]), IIf(Nz(Worker1 = 2), DLookup("FloorTech1", "WageRates", "JobNumber=" & [JobNumber]),"")

 

[TomH71]

The error I received was "Compile Error: Syntax Error"

 

[Solo712]

First of all thank you for your help. Using you advice, I managed to get the first selection to work with this code. It returned the value of 8.68.

Worker1Rate = IIf(Nz(Worker1 = 1), DLookup("SiteLead", "WageRates", "JobNumber=" & [JobNumber]), "")

HOWEVER...

Nesting the IIF caused me problems. The following did not work

Worker1Rate = IIf(Nz(Worker1 = 1), DLookup("SiteLead", "WageRates", "JobNumber=" & [JobNumber]), IIf(Nz(Worker1 = 2), DLookup("FloorTech1", "WageRates", "JobNumber=" & [JobNumber]),"")

You don't need to nest the ifs. You simply reference the listbox value in the first argument. If the string display for Worker1 is in the second column you do it like this:

 Worker1Rate = Nz(DLookup(Worker1.Column(1), "WageRates","JobNumber=" & JobNumber))

Best,
J

 

[Mihail]

Nz(Worker1 = 1)
Can someone explain how this piece of code is working ?

 

[RainLover]

Nz(Worker1 = 1)
Can someone explain how this piece of code is working ?

It would appear that some code has been posted without testing.

 

[RainLover]

I believe you need two forms. A Main and a Sub.

The Main has the details of the Job. While the sub has details of the workers and the job rates. Also perhaps the hours worked and the dates.

I would have a separate Table that lists all the various rate. This Table would be linked to the same Table as the one behind the main form.

This approach would negate the use of the DLookup and that Nested IIf.

A good look at the table design might show that some things need normalisation.