dlookup with multiple criteria

[buzzmcduffie]

I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

frmManualTaskDataEntry
[employee]
[date]
[mailcode]
[state]
[disabilityind]
[volumecode]


tblMailCodeTasks
mailcode
state
disabilityind
state
goal

 

[Daryl S]

Buzzmcduffie -

You use AND to connect the multiple criteria, and must include proper
delimeters for text and date fields. It will look something like this
(untested):

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "'")

You have two 'state' fields listed in your table. If one of them is really
date and you need to include criteria on that, then it would be like this:

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "' AND [date] = #" & me.[date] & "#")

You really should change the name of the 'date' field to something else as
this is a reserved word in Access, and it can cause problems. For now,
always put that field name in square brackets...

 

[buzzmcduffie]

What am I doing wrong??
Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Buzzmcduffie -

You use AND to connect the multiple criteria, and must include proper
delimeters for text and date fields. It will look something like this
(untested):

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "'")

You have two 'state' fields listed in your table. If one of them is really
date and you need to include criteria on that, then it would be like this:

DLookup("[goal]","[tblMailCodeTasks]","[mailcode] = '" & Me.mailcode & _
"' AND [state] = '" & me.state & "' AND [disabilityind] = '" & _
me.disabilityind & "' AND [date] = #" & me.[date] & "#")

You really should change the name of the 'date' field to something else as
this is a reserved word in Access, and it can cause problems. For now,
always put that field name in square brackets...

--
Daryl S

I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

frmManualTaskDataEntry
[employee]
[date]
[mailcode]
[state]
[disabilityind]
[volumecode]


tblMailCodeTasks
mailcode
state
disabilityind
state
goal