Dlookup by 2 fields 2

[sebell]

Hello,
I need to do a dlookup that brings back a value based on 2 different criteria

Me.txtAssignedTo2 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = " & [txtTask] & " AND [EmpNoforGroup] = " & [txtNo2])

But this is not working. Does anyone know how I can write this so that is looks as Task = txttask AND EmpNoforGroup = txtNo2

Thank you in advance for your help,
Sherry

 

[gol4]

Since both appear to be searching text data types your criteria needs to be surrounded by quotes

Me.txtAssignedTo2 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo2] "'")

 

[gol4]

Sorry droped last &
Me.txtAssignedTo2 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo2] & "'")

 

[sebell]

I tried this but is bringing in the data that matches the first criteria only.
Maybe you can help me further - I am probably doing something wrong.

My program allows you to assign Tasks to 1 to 4 employees. The task name will be the same for each employee since it is a group project. They are number 1 - 4 in the 'EmpNoforGroup' field.

When I use a dlookup it recognizes the first task it sees and will populate based on that 1st employee only which is why I was trying to dlookup based on the EmpNoforGroup.

(I hope this is making sense.)

In the form I have 3 text boxes txtNo2, txtNo3, txtNo4 with assigned default values of 2, 3, & 4 which is how I am relating this dlookup since I don't know how many people were assigned a task for the group project.

So I would have
Me.txtAssignedTo2 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo2] & "'")

Me.txtAssignedTo3 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo3] & "'")

Me.txtAssignedTo4 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", "[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo4] & "'")

But these are bringing in the same value when the criteria is met for the 1st part only.

 

[TheAceMan1]

How are ya sebell . . . .

Me.txtAssignedTo2 = DLookup("[AssignedTo]", "[tbl_ActionRegister]", [purple]"[Task] = '" & [txtTask] & "' AND [EmpNoforGroup] = '" & [txtNo2] & "'"[/purple])

See Ya! . . . . . .

 

[sebell]

Thank you both so much for your help. IT WORKS!!!!