Convert sql to vba

As requested the SQL CODE:
Code: 
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 of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], 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)]
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"));
 
Okay, try this one...

Code: 
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 of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], 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)]
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)='" & Me.MOB_SAIL_STATS & "') AND ((CIAC_DATA.Assigned_CIAC_Name)='" & Me.Process_CIACs & "') AND ((IIf([status(1)]="S" Or [status(2)]="S" Or [status(3)]="S" Or [status(4)]="S","YES","NO"))="NO"));

Can't test because this is not the same as the one I have.
 
Code: 
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 of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], 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)] " & _
"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)='" & Me.MOB_SAIL_STATS & "') AND ((CIAC_DATA.Assigned_CIAC_Name)='" & Me.Process_CIACs & "') AND ((IIf([status(1)]="S" Or [status(2)]="S" Or [status(3)]="S" Or [status(4)]="S","YES","NO"))="NO"));
I added the recordset and the breaks and the following came up Compile error:

Expected: list separator or ) which VB highlighted at the first "S".
 
Oops, fixed (forgot no quotation marks inside SQL)...

Code: 
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 of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], 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)] " & _
"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)='" & Me.MOB_SAIL_STATS & "') AND ((CIAC_DATA.Assigned_CIAC_Name)='" & Me.Process_CIACs & "') AND ((IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO'));
 
Code: 
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 of Oldest Family Member in Years], CIAC_DATA.[Home Phone Number], CIAC_DATA.[Work Phone Number], CIAC_DATA.[Cell Phone Number], 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)] " & _
"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)='" & Me.MOB_SAIL_STATS & "') AND ((CIAC_DATA.Assigned_CIAC_Name)='" & Me.Process_CIACs & "') AND ((IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO'));[COLOR=red]")[/COLOR]
I added the hightlighted part due to VB did not like it for some reason. And when I added that section Run-time error '3061': Too few parameters. Expected 2 came up.
 
Please remove the highlighted part and just add the quotation marks and let me know if it still errors.
 
As requested When I remove the highlighted section and added only the " the following happended Compile error: Expected:list separator or )
 
Okay, I think I see what's going on and now I know why you need to add the extra paranthesis. So, is this code in a Module or behind a Form? Does the Form in fact hace the Controls I referenced in the where line?
 
The code is in the module but I can put it in the form to make it easier.
 
That is why it is not working. You either need to put it behind the Form or the code has to be completely redone.
 
I added the code in the form and the stated errors happen, how easy will it be to redo the code to add it in a bas file leaving out the Me. option.
 
Not easy and one does not set the Record Source of a Form via a bas file. Why are you doing it that way? What is your reasoning?
 
The reason why I added it to a bas file was to keep most of the coding there. If it can be simplified to be put into a form it will work the same. Many Thanks.
 
Okay, well I'm going to suggest you start another thread because that's going to a lot more work and this thread has traversed from what it originally was.
 
You must log in or register to reply here.

Similar threads

G
convert raw sql to vba
Replies
10
Views
1,201
JHB
J
G
Question SQL VBA Code Not Working
Replies
8
Views
811
giddyhead
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom