Missing Operator - Dlookup

Need another pair of eyes on this, I do not see anything wrong with this statement, however, I am getting Missing Operator error.

                varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & ctl.OldValue)

The first set of Dlookups work fine, do not see any differences in the syntax.

K

For Each ctl In frm.Controls
        If ctl.Tag = "Audit" Then
            If ctl.Name = "PartnerID" Then
                varBefore = DLookup("Partner", "TL_PartnerNames", "PartnerID = " & ctl.OldValue)
                varAfter = DLookup("Partner", "TL_PartnerNames", "PartnerID = " & ctl.Value)
            ElseIf ctl.Name = "IntAgency" Then
                varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & ctl.OldValue)
                varAfter = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & ctl.Value)
            Else
                varBefore = ctl.OldValue
                varAfter = ctl.Value
            End If

Open in new window

errormsg.png
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
obviously either the OldValue or the Value of the control that is being evaluated is NULL.  Try:

varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & NZ(ctl.OldValue,0))
varAfter = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & NZ(ctl.Value,0))
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
if the field  "IntAgencyRecID" is text, use this

 varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = '" & ctl.OldValue & "'")


or


varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & chr(34) & ctl.OldValue & chr(34))
0
 
Rey Obrero (Capricorn1)Commented:


if the field  "IntAgencyRecID" is text, use this

 varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = '" & nz(ctl.OldValue) & "'")


or


varBefore = DLookup("AgencyName", "TA_PartnerInternalAgency", "IntAgencyRecID = " & chr(34) & Nz(ctl.OldValue) & chr(34))
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the great assist.

Karen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.