Dlookup Question

B

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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 
R

RonaldoOneNil

The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 
B

buzzmcduffie

I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 
R

RonaldoOneNil

Sorry missed the closing double quotes off the end

...... And Active = 'yes'")

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 
R

RonaldoOneNil

Also it needs to be all on one line unless you use the underscore
continuation character.

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 
B

buzzmcduffie

worked like a charm! thank you!

RonaldoOneNil said:
Also it needs to be all on one line unless you use the underscore
continuation character.

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

:

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

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

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

Any help is appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top