convert raw sql to vba

[giddyhead]

SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[Secondary E-mail Address], CIAC_DATA.Age_in_Years, CIAC_DATA.[Home_#], CIAC_DATA.[Work_#], CIAC_DATA.[Cell_#], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)], IIf([status(1)]="S" Or [status(2)]="S" Or [status(3)]="S" Or [status(4)]="S","YES","NO") AS Status_of_Sailor
FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name
WHERE (((CIAC_DATA.Sailor_Status)=[Forms]![CIAC]![MOB_SAIL_STATS]) AND ((CIAC_DATA.Assigned_CIAC_Name)=[Forms]![CIAC]![Process_CIACs]) AND ((IIf([status(1)]="S" Or [status(2)]="S" Or [status(3)]="S" Or [status(4)]="S","YES","NO"))="NO"));

The above raw sql is what I am attempting to put into a module or a form using an OpenRecordset to send emails to individuals that does not have a S for Successful in a drop down. Any assistance is greatly appreciated.

 

[pbaldy]

This should help:

http://www.baldyweb.com/BuildSQL.htm

 

[giddyhead]

Set rsaa = CurrentDb.OpenRecordset("SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[Secondary E-mail Address], CIAC_DATA.Age_in_Years, CIAC_DATA.[Home_#], CIAC_DATA.[Work_#], CIAC_DATA.[Cell_#], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)], IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO"") AS Sailor_Status " & _
"FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name " & _
"WHERE (((CIAC_DATA.Sailor_Status)=Forms!CIAC!MOB_SAIL_STATS) And ((CIAC_DATA.Assigned_CIAC_Name)=Forms!CIAC!Process_CIACs) And ((IIf([status(1)]=""S"" Or [status(2)]=""S"" Or [status(3)]=""S"" Or [status(4)]=""S"",""YES"",""NO""))=""NO""));")

The above is the converted SQL in VB in the bas file and when ran the following was produced Run-time error '3061' Too few parameters. Expected 4. Any pointers to where I need to look into? Thanks

 

[pbaldy]

Here's the common debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

At a glance, you aren't concatenating the form values into the SQL string. Personally I find things like

IIf([status(1)]=""S"" Or

confusing, so I'd have

IIf([status(1)]='S' Or

 

[giddyhead]

("SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Email-Address-P], MOB_SAILOR_INFO.[Email-Address-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[Secondary E-mail Address], CIAC_DATA.Age_in_Years, CIAC_DATA.[Home_#], CIAC_DATA.[Work_#], CIAC_DATA.[Cell_#], CIAC_DATA.Assigned_CIAC_Name, CIAC_FILTER.CIAC_Name, Signature_Card.Subject_Returning, Signature_Card.Returning, Signature_Card.Subject_Executing_Orders, Signature_Card.Executing_Orders, Signature_Card.Subject_Under_Orders, Signature_Card.Under_Orders, Signature_Card.IA_Family, Signature_Card.CIAC_Members, MOB_SAILOR_INFO.[Status(1)], MOB_SAILOR_INFO.[Status(2)], MOB_SAILOR_INFO.[Status(3)], MOB_SAILOR_INFO.[Status(4)], MOB_SAILOR_INFO.[FM_Status(1)], MOB_SAILOR_INFO.[FM_Status(2)], MOB_SAILOR_INFO.[FM_Status(3)], MOB_SAILOR_INFO.[FM_Status(4)], IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO') AS Test " & _
"FROM Signature_Card RIGHT JOIN (CIAC_DATA LEFT JOIN (CIAC_FILTER RIGHT JOIN MOB_SAILOR_INFO ON CIAC_FILTER.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON CIAC_DATA.Sailor_Name = MOB_SAILOR_INFO.SAILOR_NAME) ON Signature_Card.CIAC_Members = CIAC_FILTER.Assigned_CIAC_Name " & _
"WHERE (((CIAC_DATA.Sailor_Status)='" & [COLOR="Red"]Forms!CIAC!MOB_SAIL_STATS[/COLOR] & "') And ((CIAC_DATA.Assigned_CIAC_Name)='" & [COLOR="red"]Forms!CIAC!Process_CIACs[/COLOR] & "') And (([COLOR="red"]IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO')[/COLOR]);")

As stated I modified the forms and the following error Run-time error '3061' Too few parameters. Expected 2. happen.

 

[pbaldy]

As stated? No code so far had the form values concatenated. Did you try the debug technique to see the finished SQL and perhaps test it in a new query?

 

[giddyhead]

I use the technique and did use it in a new sql query which worked but when I put it in the bas file the same issue happen. In addition i am trying to figure out how to concatenate the forms? If it is easier I can post the test database so you can take a look at the code. I know there is a lot of stuff going on with it put the main thing is in the training bas file in the sendemail function.

 

[pbaldy]

You can post the db here if you want.

 

[giddyhead]

Please see attached

 

[giddyhead]

Please help at your soonest. Thanks.

 

[JHB]

There are to many nested queries, you've queries which have queries as source, which again has a query as source.
Either rewrite your query or save/insert the result in a temporary table.