dlookup with multiple criteria

  • Follow


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
0
Reply Utf 4/22/2010 1:53:02 PM

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
0
Reply Utf 4/22/2010 2:06:04 PM


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
0
Reply Utf 5/11/2010 11:04:01 AM

2 Replies
1761 Views

(page loaded in 0.417 seconds)


Reply: