sql problem

[fogal]

I have problems with the following sql statement: I am opening it in a recordset, and get the message too few parameters. missing 4.

I can not then open the recordset. I originally tried "select * from queryname", but this produced the same message, so I then copied the sql from the query into code.



sql1 = &quot;SELECT DISTINCTROW tblAdverts.AdvertID, tblAdverts.SectionID, tblAdverts.CategoryID, tblPublSectionCategory.PublClass, [txtmsg] & IIf(tblAdverts!PublID=37,' Call ',' Call me now on ') & Format([ResponseNo],'0000 000 0000') & ' Voicebox ' & Format(tblAdverts!AdvertID,'00000') & IIf(tblAdverts!responsetypeid=2,IIf(tblPublications!Rptname='rptMAC',' <@TxtStyle>Txt ',' Txt ') & [SMScode] & Format(tblAdverts!AdvertID,'00000')) AS [Text], [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)>=[Forms]![frmReports]![PublDate]) AND ((tblAdverts.PublID)=[Forms]![frmReports]![Publication]) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![SentDate]) Is Not Null) AND (([Forms]![frmReports]![Publication])<>30 And ([Forms]![frmReports]![Publication])<>49)) OR (((tblAdvPublDates.AdSentDate)=[Forms]![frmReports]![OldReports]) AND ((tblAdverts.PublID)=[Forms]![frmReports]![Publication]) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![Publication])<>30 And ([Forms]![frmReports]![Publication])<>49) AND (([Forms]![frmReports]![OldReports]) Is Not Null)) OR (((tblAdvPublDates.AdSentDate) Is Null) AND ((tblAdvPublDates.LastAppear)>=[Forms]![frmReports]![PublDate]) AND ((tblAdverts.PublID)=27 Or (tblAdverts.PublID)=28 Or (tblAdverts.PublID)=29) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![SentDate]) Is Not Null) AND&quot; & _
&quot; (([Forms]![frmReports]![Publication])=30)) OR (((tblAdvPublDates.AdSentDate)=[Forms]![frmReports]![OldReports]) AND ((tblAdverts.PublID)=27 Or (tblAdverts.PublID)=28 Or (tblAdverts.PublID)=29) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![Publication])=30) AND (([Forms]![frmReports]![OldReports]) Is Not Null)) OR (((tblAdvPublDates.AdSentDate) Is Null) AND ((tblAdvPublDates.LastAppear)>=[Forms]![frmReports]![PublDate]) AND ((tblAdverts.PublID)>=42 And (tblAdverts.PublID)<=48) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![SentDate]) Is Not Null) AND (([Forms]![frmReports]![Publication])=49)) OR (((tblAdvPublDates.AdSentDate)=[Forms]![frmReports]![OldReports]) AND ((tblAdverts.PublID)>=42 And (tblAdverts.PublID)<=48) AND ((tblAdverts.AdStatusID)=12) AND (([Forms]![frmReports]![Publication])=49) AND (([Forms]![frmReports]![OldReports]) Is Not Null));&quot;

 

[JeffTullin]

Spell any one of those fields incorrectly and you will get this kind of error.
Instead of a field name, it will think you are refering to a parameter.

[ResponseNo] looks like a parameter.
so does [SMSCode] and [txtMsg]

I am suspicious of the bit that says
(tblAdverts!AdvertID,'00000')) AS [Text],

because I would have expected that to look like
(tblAdverts!AdvertID,'00000')) AS Text,


Check those out.
If that does not help, you will simply have to hack out WHERE clauses and returned fields until you get something that does work, then put them back one by one until it breaks.

 

[Norris68]

Try using some aliasing - it will make your SQL code much easier to read. e.g.

SELECT a.AdvertID, m.[Name]
FROM tblAdverts a
INNER JOIN tblMembers m
ON m.MemberID = a.MemberID

With all those joins it may also be worth considering saving some of the subqueries as individual queries. You can treat a saved query just like another table - including aliasing it in your query. Access does get confused by very complex queries sometimes - usually of it's own making!

The use of the IN clause could simplify your code too e.g. ... AND tblAdverts.PublID IN (27, 28, 29) ...

I think 'Text' is a reserved word (as is 'Name') which is why it is enclosed in square brackets.

 

[PJStephenson]

I think the problem is the use of form controls in the WHERE part of the query. Try getting the actual figures into the sql1 variable when you define it. e.g.
'&quot; & [Forms]![frmForm1]![TextControl] & &quot;'
#&quot; & Format([Forms]![frmForm1]![DateControl], &quot;dd mmm yyyy&quot; & &quot;#
&quot; & [Forms]![frmForm1]![NumericControl] & &quot;

This will change the content of sql1 from:
&quot;Select * FROM tblTable1 WHERE startdate = [Forms]![frmForm1]![DateControl];&quot;
to:
&quot;Select * FROM tblTable1 WHERE startdate = #01 Jan 2004#;&quot;

Hopefully this should fix the error messages

Peter