If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dlookup with multiple criteria
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 |
#2
|
|||
|
|||
dlookup with multiple criteria
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 |
#3
|
|||
|
|||
dlookup with multiple criteria
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 |
Thread Tools | |
Display Modes | |
|
|