Convert sql to vba

Hope this information helps.
 

Attachments

So, if I understand correctly (because document not too clear)...

You are trying to run a query that groups by your selection and then sends an eMail to that selection?

That being so... did the line I sent work?

Exactly what is not working? Is it that there are no eMail addresses when the eMail opens up?
 
When I run the query as is it works but I want to put that same query in VBA in a recordset so emails can be sent based upon that selection. When I use the the filter line it only filter the form and does not send the emails. After the selections are made and the button process in pressed it searches the appropriate forms then puts those email address in the email.
 
Please help. I have made some change to the word document. If any questions please let me know. Thanks.
 

Attachments

You are still not using the WHERE clause I provided, that is why you are getting the error. You must use that line.
 
Like this
Code: 
Set Rsaa = CurrentDb.OpenRecordset("SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Emal], MOB_SAILOR_INFO.[Emal-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[S-EMAL], 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 " & _
"DoCmd.ApplyFilter , "(((CIAC_DATA.Sailor_Status)= '" & Me.MOB_SAIL_STATS & "') OR ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.Process_CIACs & " ') OR ((IIf(MOB_SAILOR_INFO.[status(1)]='S' Or MOB_SAILOR_INFO.[status(2)]='S' Or MOB_SAILOR_INFO.[status(3)]='S' Or MOB_SAILOR_INFO.[status(4)]='S','YES','NO'))='NO'))")
 
Nope, like this...

Code: 
Set Rsaa = CurrentDb.OpenRecordset("SELECT CIAC_DATA.Sailor_Name, CIAC_DATA.Sailor_Status, MOB_SAILOR_INFO.[Emal], MOB_SAILOR_INFO.[Emal-S], CIAC_DATA.Primary_E_mail_Address, CIAC_DATA.[S-EMAL], 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 & "') OR ((CIAC_DATA.Assigned_CIAC_Name)= '" & Me.Process_CIACs & " ') OR ((IIf(MOB_SAILOR_INFO.[status(1)]='S' Or MOB_SAILOR_INFO.[status(2)]='S' Or MOB_SAILOR_INFO.[status(3)]='S' Or MOB_SAILOR_INFO.[status(4)]='S','YES','NO'))='NO'))")
 
When I put the code in the main form the following error message occurred Run-time error '3061' Too few parameters. Expected 5. and also when it is put into a bas file the same error message occurs.
 
Some of them are only MOB_SAIL_STATS and Process_CIACs and they are on the main form. Can you take a look at the file and look at CIAC_-SQL-VBA QUERY in design view to see it in person. If anything please let me know. Thanks
 
I did not put break line but this should work...

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 ((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 [COLOR="red"]((CIAC_DATA.Assigned_CIAC_Name)='" & Me.Process_CIACs & "')[/COLOR] AND ((IIf([status(1)]='S' Or [status(2)]='S' Or [status(3)]='S' Or [status(4)]='S','YES','NO'))='NO'));")
When I put the code in and added the missing red section the following error came up Run-time error '3075': Syntax error in query expression all from the where clause.
 
I didn't mean for you to add it to what you had. I use the query you indicated and that first parameter was not in the query. I'm getting confused, did you change the query?
 
Thanks for all the help you are providing and sorry about that. The original code had it in there and upon review I added it back it got lost in translation. To possible assist I added the code and the same error arose. Hope this information helps.
 
How about this... how about you send me the database you have now so I am adjusting the correct code... :D
 
The test database that was submitted is very similar the only differences are the current one has updated names and phone numbers, etc. Please use the test database and if any questions on it please let me know. Thanks.
 
I did and I used what I thought was the right query but it can't be because the one you posted above is not the same. You canged it. Can you at least send the SQL of the query you want to work with the filters?
 
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