ACCESS FEED: [MS_AccessPros] Re: Double condition in DLookup not working

Rabu, 28 Desember 2011

[MS_AccessPros] Re: Double condition in DLookup not working

I assume the first DLookup is the issue. You aren't quite getting what should be inside quotes or out. Try:
lngL = Nz(DLookup("ID", "qCustomerListOffSellersWContactsDerived", "ContactID = " & Me.ContactID & " And Yr = " & Me.Yr), 0)

Duane Hookom
MS Access MVP

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> Good morning! Hope you all had a great Christmas.
>
> The following code was working before I added the "Yr = " & Me.Yr portion (in first If statement). Now I am getting a run type error '13' type mismatch.
>
> Both the qCustomerListOffSellersWContactsDerived.Yr and Yr in the query underlying the form are numbers. I believe that Yr has been derived in both cases with DatePart("yyyy", ...). The form has a Union query underlying it.
>
> Thanks!
> Connie
> If IsNull(Me.ID) Then
> lngL = Nz(DLookup("ID", "qCustomerListOffSellersWContactsDerived", "ContactID = " & Me.ContactID And "Yr = " & Me.Yr), 0)
> If (lngL) <> 0 Then
> strListingsRecordsource = "SELECT Listings.*, ListingContacts.ContactID, Withdrawn.WithdrawnDate " _
> & "FROM Listings INNER JOIN (ListingContacts INNER JOIN Withdrawn ON ListingContacts.ListID = Withdrawn.ListID) ON Listings.ListID = Withdrawn.ListID " _
> & "WHERE ListingContacts.ContactID = " & Me.ContactID & " AND DatePart(""yyyy"", [WithdrawnDate]) = " & Me.Yr & ";"
> DoCmd.OpenForm "Listings"
> Forms!Listings.RecordSource = strListingsRecordsource
> DoCmd.GoToControl "ListID"
> DoCmd.FindRecord (lngL)
> Else: Msgbox "This listing is not in the database. Check the Contact form for more info"
> End If
> End If
>