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

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


"buzzmcduffie" wrote:

> 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

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

"Daryl S" wrote:

> 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
>
>
> "buzzmcduffie" wrote:
>
> > 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