DLookup with Multiple Criteria

Question

I have an Access DB that has the following tables:

• 'Legal Entity Info' with [LegEntID] and [LegEntName]

• 'Policy Info' with [PolicyID] and [PolicyCarrierName]

• 'Policy Period' with [PolicyPeriodID] and [PolicyPeriod]

• 'Retention Amounts by Legal Entity' with [RetentionID], [PolicyPeriodID], [PolicyID], [LegEntID] and [RetentionAmount]

I have a form (Retention Payment Info Form), that pulls in the [PolicyPeriodID], [PolicyID], and [LegEntID] that are connected with a specific claim. I need to pull in the correct [RetentionAmount] that corresponds to the match in the 'Retention Amounts by Legal Entity' table. All three criteria ([PolicyPeriodID], [PolicyID], and [LegEntID]) have to be TRUE in order to pull in the correct [RetentionAmount].

On this form, I have an unbound field to show the [RetentionAmount] and I’m trying to write a DLookup function to select the [RetentionAmount] based on the selections on the form. Here is what I think it should look like, however, it’s not working.

=DLookUp("[RetentionAmount]", 
    "Retention Amounts by Legal Entity", 
    "[PolicyPeriodID]='" &  Forms![Retention Payment Info Form].Form.[PolicyPeriodID] & 
    "' AND [PolicyID]='" &  Forms![Retention Payment Info Form].Form.[PolicyID] & 
    "' AND [LegEntID]='" &  Forms![Retention Payment Info Form].Form.[LegEntID] & 
    "'")

Any suggestions?

You can replace .Form. by ! in all 3 expressions, but that shouldn't make a difference. If these IDs are numbers, remove the single quotes. Else: please clarify "it’s not working.". — Andre
All three IDs are numbers, so I removed the single quotes. That didn't change anything. I then replaced the.Form with !, and it is now working. Thank you so much! — DAldrich

Andre Andre · Accepted Answer · 2016-03-09T18:19:14

.Form is only needed when working with subforms.

http://access.mvps.org/access/forms/frm0031.htm is a good reference.
(You are here: Forms!Mainform!ControlName)

Correct syntax:

=DLookUp("[RetentionAmount]", 
    "Retention Amounts by Legal Entity", 
    "[PolicyPeriodID]=" & Forms![Retention Payment Info Form]![PolicyPeriodID] & 
    " AND [PolicyID]=" & Forms![Retention Payment Info Form]![PolicyID] & 
    " AND [LegEntID]=" & Forms![Retention Payment Info Form]![LegEntID])

DLookup with Multiple Criteria

1 Answers