Problems with where statement

[fogal]

I have problems with the following where statement in my sql query. I am trying to open a query and sql1 is the where statement belonging to that query. sql5 and sql6 are used in sql1. I get the error no current record found. The query was a working query and I copied and amended the sql from sql view in the existing query.

sql5 = IIf(IsNull([Forms]![frmReports]![OldReports]), "((tblAdvPublDates.AdSentDate)=Null)", "((tblAdvPublDates.AdSentDate)=#" & [Forms]![frmReports]![OldReports] & "#)&quot
sql6 = IIf(IsNull([Forms]![frmReports]![OldReports]), "(Null Is Not Null)", "(#" & [Forms]![frmReports]![OldReports] & "# Is Not Null)&quot


sql1 = &quot;SELECT DISTINCTROW tblAdverts.AdvertID, tblAdverts.SectionID, tblAdverts.CategoryID, tblPublSectionCategory.PublClass, [tblAdverts]![txtmsg] & IIf(tblAdverts!PublID=37,' Call ',' Call me now on ') & Format([tblPublications]![ResponseNo],'0000 000 0000') & ' Voicebox ' & Format(tblAdverts!AdvertID,'00000') & IIf(tblAdverts!responsetypeid=2,IIf(tblPublications!Rptname='rptMAC',' <@TxtStyle>Txt ',' Txt ') & [tblPublications]![SMScode] &quot; & _
&quot; & Format(tblAdverts!AdvertID,'00000')) AS [Text], [tblAdverts]![txtmsg] & ' Call me now on 1570 600720 Voicebox ' & Format(tblAdverts!AdvertID,'00000') AS TextIoS, tblAdverts.AdInputDate, tblMembers.DateOfBirth, tblStatus.Status, tblPublAreas.PublAreaRpt, tblMembers.Name, tblMembers.Surname, tblMembers.Postcode, tblMembers.Address1, tblMembers.Address2, tblMembers.Address3, tblMembers.TelNo, tblAdvPublDates.AdvProdID, tblCreditCardTypes.CreditCardType, tblPayments.CreditCardNo, tblPayments.ExpiryDate, tblAreas.Area, tblPublications.SMScode, tblAdverts.ResponseTypeID, tblAdverts.TxtMsg&quot; & _
&quot; FROM (((tblAreas RIGHT JOIN ((((((tblAdverts LEFT JOIN tblPublSectionCategory ON (tblAdverts.PublID = tblPublSectionCategory.PublID) AND (tblAdverts.SectionID = tblPublSectionCategory.SectionID) AND (tblAdverts.CategoryID = tblPublSectionCategory.CategoryID)) INNER JOIN tblAdvPublDates ON tblAdverts.AdvertID = tblAdvPublDates.AdvertID) INNER JOIN tblPublications ON tblAdverts.PublID = tblPublications.PublID) INNER JOIN tblMembers ON tblAdverts.MemberID = tblMembers.MemberID) INNER JOIN tblStatus ON tblAdvPublDates.StatusID = tblStatus.StatusID) LEFT JOIN tblPublAreas ON (tblAdverts.AreaID = tblPublAreas.AreaID) AND (tblAdverts.PublID = tblPublAreas.PublID)) ON tblAreas.AreaID = tblAdverts.AreaID) LEFT JOIN tblAdvProducts ON tblAdvPublDates.AdvProdID = tblAdvProducts.AdvProdID) LEFT JOIN tblPayments ON tblAdvProducts.PaymentID = tblPayments.PaymentID) LEFT JOIN tblCreditCardTypes ON tblPayments.CreditCardTypeID = tblCreditCardTypes.CreditCardTypeID&quot; & _
&quot; WHERE (((tblAdvPublDates.AdSentDate) Is Null) AND ((tblAdvPublDates.LastAppear)>=#&quot; & [Forms]![frmReports]![PublDate] & &quot;#) AND ((tblAdverts.PublID)=&quot; & [Forms]![frmReports]![Publication] & &quot AND ((tblAdverts.AdStatusID)=12) AND ((#&quot; & [Forms]![frmReports]![SentDate] & &quot;#) Is Not Null) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot<>30 And (&quot; & [Forms]![frmReports]![Publication] & &quot<>49)) or (&quot; & sql5 & &quot; And ((tblAdvPublDates.AdSentDate)=null) AND ((tblAdverts.PublID)=&quot; & [Forms]![frmReports]![Publication] & &quot AND ((tblAdverts.AdStatusID)=12) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot<>30 And (&quot; & [Forms]![frmReports]![Publication] & &quot<>49) AND &quot; & sql6 & &quot &quot; & _
&quot;OR (((tblAdvPublDates.AdSentDate) Is Null) AND ((tblAdvPublDates.LastAppear)>=#&quot; & [Forms]![frmReports]![PublDate] & &quot;#) AND ((tblAdverts.PublID)=27 Or (tblAdverts.PublID)=28 Or (tblAdverts.PublID)=29) AND ((tblAdverts.AdStatusID)=12) AND ((#&quot; & [Forms]![frmReports]![SentDate] & &quot;#) Is Not Null) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot=30)) &quot; & _
&quot;OR (&quot; & sql5 & &quot; AND ((tblAdverts.PublID)=27 Or (tblAdverts.PublID)=28 Or (tblAdverts.PublID)=29) AND ((tblAdverts.AdStatusID)=12) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot=30) And &quot; & sql6 & &quot &quot; & _
&quot;OR (((tblAdvPublDates.AdSentDate) Is Null) AND ((tblAdvPublDates.LastAppear)>=#&quot; & [Forms]![frmReports]![PublDate] & &quot;#) AND ((tblAdverts.PublID)>=42 And (tblAdverts.PublID)<=48) AND ((tblAdverts.AdStatusID)=12) AND ((#&quot; & [Forms]![frmReports]![SentDate] & &quot;#) Is Not Null) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot=49)) &quot; & _
&quot;OR (&quot; & sql5 & &quot;AND ((tblAdverts.PublID)>=42 And (tblAdverts.PublID)<=48) AND ((tblAdverts.AdStatusID)=12) AND ((&quot; & [Forms]![frmReports]![Publication] & &quot=49) And &quot; & sql6 & &quot;&quot;

 

[lameid]

I suggest using debug.print to print the resulting SQL string to the debug window and put it in the SQL view of a query and switch to design view may help... Chances are you will get a message like the query is too complex if you try to run the final statement. If it is too complex, you may have to nest query objects. If it still executes, posting the final SQL string will likely help us read it.

 

[TonyJollans]

Hi fogal,

It is a bit complex, and I haven't studied it fully, but ..

[purple]((tblAdvPublDates.AdSentDate)=Null)[/purple]

.. in SQL5 might be better as ..

[blue](IsNull(tblAdvPublDates.AdSentDate))[/blue]

And I have no idea what you expect from ..

[purple]&quot;(Null Is Not Null)&quot;[/purple]

.. in SQL6

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.