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

Rabu, 28 Desember 2011

RE: [MS_AccessPros] Double condition in DLookup not working

Maybe try

AND DatePart(""yyyy"", [WithdrawnDate]) = #" & Me.Yr & "#;"

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Wednesday, December 28, 2011 10:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Double condition in DLookup not working

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

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.